테이블 스페이스 용량 확인 쿼리

매일 보거나 일정한 간격으로  볼려면 cron에  등록해서 돌리면 된다.


#!/bin/sh
# 오늘 날짜를 셸 변수로 지정
TODAY=`date +"%Y%m%d"`

sqlplus "/as sysdba" << ENDSQL

SET LINESIZE 500
COLUMN TableSpaceName FORMAT A15
COLUMN "전체 용량" FORMAT A10
COLUMN "사용량" FORMAT A10
COLUMN "사용량 비율" FORMAT A12
COLUMN "남은 공간" FORMAT A12
COLUMN "남는 공간 비율" FORMAT A15
COLUMN CheckDate FORMAT A10
column date_column new_value today_var

SPOOL DBSIZE_${TODAY}_magic.txt


select  TableSpaceName
        ,sum(TotalMemory)||'MB' as "전체 용량"
        ,sum(UsedMegaBytes)||'MB' as "사용량"
        ,round(100*(sum(UsedMegaBytes))/sum(TotalMemory),0)||'%' as "사용량 비율"
        ,(sum(TotalMemory)-sum(UsedMegaBytes))||'MB' as "남은 공간"
        ,100-round(100*(sum(UsedMegaBytes))/sum(TotalMemory),0)||'%' as "남는 공간 비율"
        ,SYSDATE CheckDate
from (
SELECT
           A.TABLESPACE_NAME TableSpaceName,
           ROUND(A.BYTES / 1024 / 1024, 0) TotalMemory,
           ROUND(((A.BYTES - SUM(NVL(B.BYTES,0)))) / 1024 / 1024, 1) UsedMegaBytes,
           ROUND((SUM(NVL(B.BYTES,0))) / 1024 / 1024 ,1) FreeMegaBytes,
           ROUND((SUM(NVL(B.BYTES,0)) / (A.BYTES)) *100 ,1) FreePercent,
           A.FILE_NAME DataFile,
           SYSDATE CheckDate
FROM
   DBA_DATA_FILES A RIGHT OUTER JOIN DBA_FREE_SPACE B ON (A.FILE_ID = B.FILE_ID)
GROUP BY A.TABLESPACE_NAME, A.FILE_NAME, A.BYTES
)
group by TableSpaceName
order by 1;
SPOOL OFF

ENDSQL