Below query gives us detailed information about tablespace usageset pagesize 100
| set linesize 200 break on report compute sum of free_mb on report compute sum of act_size on report compute sum of used_mb on report col %used format 999.99 col %Free format 999.99 col act_size format 99,99,999.99 col free_mb format 99,99,999.99 col used_mb format 99,99,999.99 col largest_extent_MB format 99,99,999.99select SUBSTR(a.tablespace_name,1,20) “TABLESPACE”,act_size,free_mb, free_mb*100/act_size “%FREE”, act_size-free_mb used_MB,((act_size-free_mb)*100)/act_size “%USED”,largest_extent_MB from (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB, max(bytes)/1024/1024 largest_extent_MB FROM DBA_FREE_SPACE group by tablespace_name) a, (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 ACT_SIZE FROM DBA_DATA_FILES group by tablespace_name) b where a.tablespace_name=b.tablespace_name order by 1; |
Output:
Last Updated: August 05, 2020
No Responses