[ORACLE]Tablespace별로 용량 체크하기

SELECT 
    SUBSTR(MAX(A.TABLESPACE_NAME),1,16) "Tablespace",
    --TO_CHAR(A.FILE_ID, '9999') "File ID",
    SUBSTR(MAX(A.FILE_NAME),1,43) "Data file",
    SUBSTR(MAX(A.STATUS),1,10) "Status",
    MAX(A.BYTES)/1024/1024 "TOTAL SIZE(Mb)" ,
    (MAX(A.BYTES)-NVL(SUM(B.BYTES),0))/1024/1024 "USED SIZE(Mb)",
    NVL(SUM(B.BYTES),0)/1024/1024 "FREE SIZE(Mb)",    
    --MAX(A.BLOCKS)-NVL(SUM(B.BLOCKS),0) "USED BLOCKS",
    TO_CHAR((MAX(A.BYTES)-NVL(SUM(B.BYTES),0))*100/MAX(A.BYTES),'990.99')||'%' "USED USAGE",
    --NVL(SUM(B.BLOCKS),0) "FREE BLOCKS",
    TO_CHAR(NVL(SUM(B.BYTES),0)*100/MAX(A.BYTES),'990.99')||'%' "FREE USAGE"
    --MAX(A.BLOCKS) "TOTAL BLOCKS"
FROM DBA_DATA_FILES A, DBA_FREE_SPACE B
WHERE A.FILE_ID=B.FILE_ID(+)
--AND   A.FILE_ID IN ('6','7','8')
GROUP BY A.FILE_ID
ORDER BY 1,2;

 

 

SELECT U.TABLESPACE_NAME "테이블 스페이스"
, U.BYTES / 1048576 "크기(mb)"
, (U.BYTES - SUM(NVL(F.BYTES,0))) / 1048576 "사용됨(mb)"
, (SUM(NVL(F.BYTES,0))) / 1048576 "남음(mb)"
, TRUNC((SUM(NVL(F.BYTES,0)) / U.BYTES) * 100,2) "남은 %"
, U.FILE_NAME "저장위치"
FROM DBA_FREE_SPACE F
, DBA_DATA_FILES U
WHERE F.FILE_ID(+) = U.FILE_ID
GROUP BY U.TABLESPACE_NAME
, U.FILE_NAME
, U.BYTES
ORDER BY U.TABLESPACE_NAME;

 

 

select file_name, tablespace_name, bytes, autoextensible
  from dba_data_files
  where tablespace_name='AMAIL_DATA_TS';