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: