꿈꾸는 개발자, DBA 커뮤니티 구루비

테이블스페이스명과 데이터파일의 위치 데이터파일의 크기와 사용한 크기 남은공간, AutoExtend상태와 Status를 볼 수 있다.

 
COL TABLESPACE_NAME FOR A15
COL FILE_NAME FOR A38
SET LINESIZE 120
SET PAGESIZE 60
COL FILE_SIZE FOR A9
COL FREE_SPACE FOR A8
COL USED_SPACE for a10
COL autoextensible for a6
COL status for a10
COL pct_free for a8
COL c0 noprint

SELECT NVL(SUM(B.BYTES/1048576),0) / A.BYTES/1048576 c0,
       A.tablespace_name,  A.file_name,
       to_char(TRUNC(NVL(SUM(B.BYTES/1048576),0) /
       (A.BYTES/1048576),4)*100)||'%' pct_free,
       NVL(TRUNC(SUM(B.BYTES/1048576),2),0)||'MB' FREE_SPACE,
       TRUNC((A.BYTES/1048576)-NVL(SUM(B.BYTES/1048576),2),0)||'MB' USED_SPACE,
       TRUNC(A.BYTES/1048576,2)||'MB' FILE_SIZE, autoextensible,   status
FROM   DBA_DATA_FILES A, DBA_FREE_SPACE B
WHERE  A.FILE_ID=B.FILE_ID (+)
GROUP BY A.tablespace_name, A.file_name,
         A.bytes/1048576,autoextensible,status,A.BYTES
UNION
SELECT NVL(SUM(BB.BYTES/1048576),0) / AA.BYTES/1048576 c0,
       AA.tablespace_name, AA.file_name,
       to_char(TRUNC(NVL(SUM(BB.BYTES/1048576),0) / (AA.BYTES/1048576),4)*100)||'%' pct_free,
       NVL(TRUNC(SUM(BB.BYTES/1048576),2),0)||'MB' FREE_SPACE,
       TRUNC((AA.BYTES/1048576)-NVL(SUM(BB.BYTES/1048576),2),0)||'MB' USED_SPACE,
       TRUNC(AA.BYTES/1048576,2)||'MB' FILE_SIZE, autoextensible, status
FROM   DBA_TEMP_FILES AA, DBA_FREE_SPACE BB
WHERE  AA.FILE_ID=BB.FILE_ID (+)
GROUP BY AA.tablespace_name, AA.FILE_NAME,AA.BYTES/1048576,
         autoextensible, status, AA.BYTES
ORDER BY 1