|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT T.NAME , T.BYTES,T.USED,T.FREE,T.PCT_FREE_USED, C.BLOCK_SIZE/1024||'k' BLOCK_SIZE, C.CONTENTS, C.LOGGING, C.EXTENT_MANAGEMENT, C.ALLOCATION_TYPE, C.SEGMENT_SPACE_MANAGEMENT, C.DEF_TAB_COMPRESSION FROM ( SELECT A.TABLESPACE_NAME NAME, SUM(B.BYTES/1048576)/COUNT( DISTINCT A.FILE_ID||'.'||A.BLOCK_ID ) BYTES, SUM(B.BYTES/1048576)/COUNT( DISTINCT A.FILE_ID||'.'||A.BLOCK_ID ) - SUM(A.BYTES/1048576)/COUNT( DISTINCT B.FILE_ID ) USED, SUM(A.BYTES/1048576)/COUNT( DISTINCT B.FILE_ID ) FREE, 100-(100 * ( (SUM(B.BYTES)/COUNT( DISTINCT A.FILE_ID||'.'||A.BLOCK_ID )) - (SUM(A.BYTES)/COUNT( DISTINCT B.FILE_ID ) )) / (SUM(B.BYTES)/COUNT( DISTINCT A.FILE_ID||'.'||A.BLOCK_ID ))) PCT_FREE_USED FROM DBA_FREE_SPACE A, DBA_DATA_FILES B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME GROUP BY A.TABLESPACE_NAME ) T , DBA_TABLESPACES C WHERE T.NAME=C.TABLESPACE_NAME ORDER BY T.PCT_FREE_USED ; |