테이블스페이스 사용량(여유율) 확인 쿼리

2013.03.20 14:05

ㅇ 테이블스페이스 사용량 확인 쿼리(1)

set verify off linesize 180
col tablespace_name format a14
SELECT
"Check Time"
,TS_INFO.tablespace_name
,TS_INFO."TOTAL SIZE(MB)"
,TS_INFO."USED(MB)"
,TS_INFO."AVAIL(MB)" "Tot_AVAIL(MB)"
,TO_CHAR((MAX(DFS.BYTES)/1024/1024),'999,990.999') "Act_AVAIL(MB)"  -- max Extent size on The tablespace
,TS_INFO."USED %"
,TS_INFO."FREE %"
,TS_INFO."STATUS"
FROM (
SELECT
SYSDATE "Check Time"
,tablespace_name
,sum(total_size)/1024 "TOTAL SIZE(MB)"
,TO_CHAR(round((sum(used)/1024),4),'999,990.999') "USED(MB)"
,TO_CHAR((sum(total_size)/1024)-(round((sum(used)/1024),4)),'999,990.999') "AVAIL(MB)"
,TO_CHAR(round((sum(used)/sum(total_size)),4)*100,'990.99') "USED %"
,TO_CHAR(round(1-(sum(used)/sum(total_size)),4)*100,'909.99') "FREE %"
,CASE WHEN  (round(1-(sum(used)/sum(total_size)),4)*100) <= '10' THEN ' Alert'
      WHEN  (round(1-(sum(used)/sum(total_size)),4)*100) <= '15' THEN ' Warning'
      WHEN  (round(1-(sum(used)/sum(total_size)),4)*100) <= '30' THEN ' Normal'
      ELSE ' Good' END "STATUS"
FROM (
SELECT
                  b.tablespace_name , -- tablespace Name
                  b.bytes / 1024 total_size, -- 총 Bytes
                  ((b.bytes - sum(nvl(a.bytes,0)))) / 1024 used, -- 사용한 용량
                  (sum(nvl(a.bytes,0))) / 1024 free_size -- 남은 용량
                  FROM DBA_FREE_SPACE a, DBA_DATA_FILES b
WHERE a.file_id(+) = b.file_id
AND
b.tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces)
                  GROUP BY b.tablespace_name, b.file_name, b.bytes
)
GROUP BY tablespace_name
) TS_INFO,
DBA_FREE_SPACE DFS 
WHERE TS_INFO.TABLESPACE_NAME = DFS.TABLESPACE_NAME
GROUP BY TS_INFO.tablespace_name,"TOTAL SIZE(MB)","USED(MB)","AVAIL(MB)","USED %","FREE %","STATUS","Check Time"
ORDER BY "FREE %"; 


쿼리 결과





ㅇ 테이블스페이스 사용량 확인 쿼리(2)


확인하고 싶은 사용률의 기준값(예:90)을 입력하고 그 이상인 테이블스페이스만 간략히 보여주는 쿼리

set verify off linesize 100

ACCEPT vLIMIT NUMBER DEFAULT 0 PROMPT 'TABLESPACE USAGE PERCENT >= : '
COL TODAY FORMAT A20 NEW_VALUE CURR_TIME
COL TODAY NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS' ) TODAY FROM DUAL;
PROMPT ========================================================
PROMPT ==  FREE SPACE USAGE PCT% FOR EACH TABLESPACE
PROMPT => DATE : [ &CURR_TIME ]
PROMPT ========================================================
COL TABLESPACE_NAME FORMAT A13
COL FILE_NAME FORMAT A30
COL FREE_MB  FORMAT 999,999,999
COL TOT_MB   FORMAT 999,999,999
COL "USAGE%" FORMAT 999.9
SELECT A.TABLESPACE_NAME,
                        SUM(A.BYTES)/(1024*1024) AS TOT_MB,
                        NVL(SUM(SZ_MB),0) AS FREE_MB,
       NVL(( 1. - SUM(SZ_MB)/(SUM(A.BYTES)/(1024.*1024.)) ) * 100,100) AS "USAGE%"
FROM DBA_DATA_FILES A,
  ( SELECT FILE_ID  , SUM(BYTES)/(1024.*1024.) AS SZ_MB
    FROM DBA_FREE_SPACE
    GROUP BY FILE_ID ) B
WHERE A.FILE_ID = B.FILE_ID (+)
GROUP BY A.TABLESPACE_NAME
HAVING NVL(( 1. - SUM(SZ_MB)/(SUM(A.BYTES)/(1024.*1024.)) ) * 100,100) >= &vLIMIT
;


쿼리 결과

SQL> @tbs_usage2.sql

TABLESPACE USAGE PERCENT >= : 80


========================================================

==  FREE SPACE USAGE PCT% FOR EACH TABLESPACE

=> DATE : [ 2013/03/20 14:01:06 ]

========================================================


TABLESPACE_NA       TOT_MB      FREE_MB USAGE%

------------- ------------ ------------ ------

RND_DATA           100,000       13,863   86.1

USERS                   61            4   93.4




쿼리는 첨부파일 참고. 


tbs_usage1.sql


tbs_usage2.sql

 

 


또루아빠 SQL(오라클,mysql) , , ,