TABLESPACE 당 사용율 조회

2011-10-14 09:42:08

 

---

SQL 4 Oracle 오라클용 sql 실무
▒ TABLESPACE 당 사용율 조회

 

▒ sys유저 권한이 사용가능할 경우
SELECT TABLESPACE_NAME,

             SUM(T_BYTES) "총용량(MB)", 

             SUM(T_USED) "사용중인공간(MB)",

             SUM(T_FREE) "사용가능공간(MB)"
FROM (SELECT B.TABLESPACE_NAME,

                        ROUND(B.BYTES/1024/1024,2) T_BYTES,
                        ROUND((B.BYTES - SUM(NVL(A.BYTES,0)))/1024/1024, 2) T_USED,
                        ROUND(SUM(NVL(A.BYTES,0))/1024/1024, 2) T_FREE
           FROM SYS.DBA_FREE_SPACE A, SYS.DBA_DATA_FILES B
           WHERE A.FILE_ID(+) = B.FILE_ID
           GROUP BY B.TABLESPACE_NAME, B.FILE_ID, B.FILE_NAME, B.BYTES )
GROUP BY TABLESPACE_NAME
ORDER BY TABLESPACE_NAME