Query:
| set pagesize 60 column “Tablespace” heading “Tablespace Name” format a30 column “Size” heading “Tablespace|Size (mb)” format 9999999.9 column “Used” heading “Used|Space (SQL> mb)” format 9999999.9 column “Left” heading “Available|Space (mb)” format 9999999.9 column “PCTFree” heading “% Free” format 999.99 ttitle left “Tablespace Space Allocations” break on reportSELECT /*+ RULE */ T.TABLESPACE_NAME, NVL(ROUND(((SUM(U.BLOCKS)*P.VALUE)/1024/1024), 2),0) USED_MB, T.TOT_MB, NVL(ROUND(SUM(U.BLOCKS)*P.VALUE/1024/1024/T.TOT_MB * 100,2),0) “USED %” FROM GV$SORT_USAGE USQL> , GV$PARAMETER P, (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TOT_MB FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) T WHERE P.NAME = ‘db_block_size’ AND U.TABLESPACE (SQL> +) = T.TABLESPACE_NAME GROUP BY T.TABLESPACE_NAME, P.VALUE, T.TOT_MB ORDER BY 1, 2;ttitle off clear columns clear computes clear breaks |
Result:
| TABLESPACE_NAME USED_MB TOT_MB USED % —————————— ———- ———- ———————– TEMP 100 500 20 |
No Responses