These queries provide insights into tablespace utilization, helping you identify potential storage issues :
Query 1: Check the tablespace utilization :
SELECT TABLESPACE_NAME, USED_SPACE,TABLESPACE_SIZE,USED_PERCENT FROM DBA_TABLESPACE_USAGE_METRICS ORDER BY USED_PERCENT DESC;Query 2: Check the detail of tablespaces used, free and percentage :
Select f.tablespace_name , to_char(t.total_space,'9999,9999') "TOTAL(MB)" , to_char((t.total_space-f.free_space),'9999,9999')"USED(MB)", to_char(f.free_space,'999,999') "FREE(MB)", to_char((round(((t.total_space-f.free_space)/t.total_space)*100)),'999')||'%' PER_USED, to_char((round((f.free_space/t.total_space)*100)),'999')||'%'PER_FREE from (select tablespace_name , round (sum(blocks * ( select value/1024 from v$parameter where name='db_block_size')/1024)) free_space from dba_free_space group by tablespace_name)f , (select tablespace_name , round(sum(bytes/1048576)) total_space from dba_data_files group by tablespace_name ) t where f.tablespace_name=t.tablespace_name ;
Query 3: Give space utilization of datafiles present in tablespaces :
select tablespace_name , file_name, autoextensible , bytes/1024/1024 "USED SPACE(MB)", maxbytes/1024/1024 " MAX SIZE(MB) " from dba_data_files order by tablespace_name,file_name ;