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: