set linesize 150 column tablespace_name format a20 heading 'Tablespace' column sumb format 999,999,999 column extents format 9999 column bytes format 999,999,999,999 column largest format 999,999,999,999 column Tot_Size format 999,999 Heading 'Total| Size(Mb)' column Tot_Free format 999,999,999 heading 'Total Free(MB)' column Pct_Free format 999.99 heading '% Free' column Chunks_Free format 9999 heading 'No Of Ext.' column Max_Free format 999,999,999 heading 'Max Free(Kb)' set echo off PROMPT FREE SPACE AVAILABLE IN TABLESPACES select a.tablespace_name,sum(a.tots/1048576) Tot_Size, sum(a.sumb/1048576) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free, sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free from ( select tablespace_name,0 tots,sum(bytes) sumb, max(bytes) largest,count(*) chunks from dba_free_space a group by tablespace_name union select tablespace_name,sum(bytes) tots,0,0,0 from dba_data_files group by tablespace_name) a group by a.tablespace_name order by pct_free; Total Tablespace Size(Mb) Total Free(MB) % Free Max Free(Kb) No Of Ext. -------------------- --------- -------------- ------- ------------ ---------- SYSTEM 790 3 .38 3,008 2 SYSAUX 752 52 6.86 32,768 132 USERS 5 1 11.25 576 1 MGMT_ECM_DEPOT_TS 100 43 43.25 43,968 2 MGMT_TABLESPACE 13,940 8,388 60.17 155,200 1594 UNDOTBS1 605 491 81.07 311,360 44 PATROL 1 1 93.75 960 1
Script – Tablespace free space and fragmentation
最新推荐文章于 2019-04-15 17:31:53 发布