Script to check all Tablespaces Usage
Script to check all Tablespaces Usage
select df.tablespace_name "TABLESPACE",df.totalspace "TOTAL_SIZE_MB",totalusedspace "USED_SIZE_MB",(df.totalspace - tu.totalusedspace) "FREE_SIZE_MB",round(100 * ( (tu.totalusedspace)/ df.totalspace))"USED_PCT" from(select tablespace_name,round(sum(bytes) / 1048576) TotalSpacefrom dba_data_filesgroup by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_namefrom dba_segmentsgroup by tablespace_name) tuwhere df.tablespace_name = tu.tablespace_nameunionSELECT a.tablespace_name,ROUND((c.total_blocks*b.block_size)/1024/1024,2)"TOTAL_SIZE_MB",ROUND((a.used_blocks*b.block_size)/1024/1024,2) "USED_SIZE_MB",ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024,2) "FREE_SIZE_MB",ROUND((a.used_blocks/c.total_blocks)*100,2) "USED_PCT"FROM v$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)total_blocks FROM dba_temp_files GROUP by tablespace_name) cWHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name order by 5 desc;