CODEWITHSUNDEEP
sqloracle-database
Rajesh
Rajesh

Reputation: 692

How to check and calculate the complete size of oracle database, tablespaces, schemas, objects, recycle bin and more?

This question answer is already available in segments over the internet with a more informative title. Aspirants mostly looking for a different type of size accumulation:

And we will keep on update this Solution for more helpful, thank!

Upvotes: 1

Views: 9478

Answers (1)

Rajesh
Rajesh

Reputation: 692

Here are a useful set of SQL statements:

# Complete Size of Database on a physical disk (unless you are using ASM).

select sum(bytes / (1024*1024)) "DB Size in MB" from dba_data_files;
select sum(bytes / (1024*1024*1024)) "Size in GB" from dba_data_files;
select sum(bytes / (1024*1024*1024)) "Size in GB" from dba_temp_files;
select (select sum(bytes)/1024/1024/1024 from dba_data_files) + (select sum(bytes)/1024/1024/1024 from dba_temp_files) "Size in GB" from dual;

# Actual space used by complete DB, all schemas, specific user, objects and quota space.

select sum(bytes)/1024/1024/1024 "Size in GB" from dba_segments;
select owner, sum(bytes)/1024/1024 "Size in MB" from dba_segments group  by owner;
select owner, sum(bytes)/1024/1024 "Size in MB" from dba_segments where owner='SYSMAN' group  by owner;
select segment_name, segment_type, bytes/1024/1024 "Size in MB" from dba_segments where segment_type='MATERIALIZED VIEW' and segment_name='MPC_TIME_WRITING_ALL_MV';
--Size quota space
select username, tablespace_name, sum(bytes)/1024/1024 "Size in MB" from dba_ts_quotas where username in ('SYSMAN') group by username, tablespace_name;

# Space occupied by dba_recyclebin for all DB and users

--Fyi,  SHOW PARAMETERS db_block_size
--      1 block = 512 bytes         
select owner, type, count(*) from dba_recyclebin group by owner, type;  
select owner, (sum(space)*512)/1024/1024 "Size in MB" from dba_recyclebin group by owner order by sum(space);
select owner, (sum(space)*512)/1024/1024 "Size in MB" from dba_recyclebin where owner='SYSMAN' group by owner order by sum(space);

# Size of physical and temporary tablespace with calculated details

--Physical tablespace
SELECT tablespace_name, SUM(bytes)/1024/1024 "Used in MB", SUM(maxbytes)/1024/1024 "Max in MB" FROM dba_data_files GROUP BY tablespace_name;
SELECT tablespace_name, SUM(bytes)/1024/1024 "Used in MB", SUM(maxbytes)/1024/1024 "Max in MB" FROM dba_data_files WHERE tablespace_name='SYSAUX' GROUP BY tablespace_name;

--Temporary tablespace
SELECT tablespace_name, SUM(bytes)/1024/1024 "Used in MB", SUM(maxbytes)/1024/1024 "Max in MB" FROM dba_temp_files GROUP BY tablespace_name;
SELECT tablespace_name, SUM(bytes)/1024/1024 "Used in MB", SUM(maxbytes)/1024/1024 "Max in MB" FROM dba_temp_files WHERE tablespace_name='SYS_TEMP' GROUP BY tablespace_name;

--Calculated details
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
   ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
   FROM dba_data_files
   GROUP BY tablespace_name) df,
  (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
    ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
    FROM dba_free_space
    GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;

The above list of SQL statements are gist to help you achieve your result, You may alter them as per your requirement.

Thanks!

Upvotes: 2

Related Questions