Home > General, Oracle > Database Size

Database Size

dba_data_files –  means total size of the data file.

The data file size is total of dba_free_space and dba_segments:
dba_data_files = dba_free_space + dba_segments

dba_segments – means used size of the data file

dba_free_space – means free size of the data file

Size Check Queries:

To get total datafiles size, free and used space in percentage:

select t2.total "TOTAL SIZE",t1.free "FREE SPACE",
 (t1.free/t2.total)*100 "FREE (%)",
 (1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 ,
 (select sum(bytes)/1024/1024 total from dba_Data_files) t2;

To get the size of all Data files:

select sum(bytes)/1024/1024 "Data files size in MB" from dba_data_files;

To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Temp files size in MB" from dba_temp_files;

To get the size of the on-line redo-logs:

SELECT SUM(BYTES)/1024/1024 "Redo files size in MB" FROM SYS.V_$LOG;

Total Size and free size:

select round(sum(used.bytes) / 1024 / 1024 ) || ' MB' "Database Size"
, round(free.p / 1024 / 1024) || ' MB' "Free space"
from (select bytes from v$datafile
 union all
 select bytes from v$tempfile
 union all
 select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p;

Individual tablespace size:

select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Size"
from dba_data_files
 group by tablespace_name
 ORDER BY 1;

Individual tablespace used size:

select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Used Size"
from dba_segments
 GROUP BY TABLESPACE_NAME
 order by 1;

Individual tablespace free space:

select tablespace_name as "Tablespace Name", sum(bytes)/(1024*1024) as "Free Space"
 from dba_free_space
 group by tablespace_name
 order by 1;

Reference Links:

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: