[ORACLE] UNDO 사용량 조회

쿼리 1

※ expired extent : undo_retention 시간을 초과한 Extent.사용 중인 트랜잭션이 없고 undo retention 도 완료되어서 언제든지 트랜잭션에 할당될 수 있는 상태 
※ unexpired extent : undo_retention 시간을 초과하지 않은 Extent. 사용 중인 트랜잭션이 없으나, 언두 유지 시간이 완료되지 않아서 트랜잭션에 할당되지 않고 보존되어 있는 상태
※ active extent :트랜잭션에 할당되어 언두 데이터를 기록하고 있는 상태
※ free extent : 생성 이후 트랜잭션에 한 번도 할당되지 않았거나, SMON에 의한 주기적 정리가 완료되어 있는 상태

SELECT A.TABLESPACE_NAME,A.STATUS, B.TOTAL_MB, A.USE_MB,
       ROUND(RATIO_TO_REPORT(A.USE_MB) OVER(PARTITION BY A.TABLESPACE_NAME) * 100)||'%' AS PCT
  FROM (SELECT TABLESPACE_NAME, STATUS,
               ROUND(SUM(BYTES/1024/1024)) USE_MB
          FROM DBA_UNDO_EXTENTS
         GROUP BY TABLESPACE_NAME,STATUS
       )A,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024) TOTAL_MB
          FROM DBA_DATA_FILES
         WHERE TABLESPACE_NAME LIKE 'UNDO%'
         GROUP BY TABLESPACE_NAME
       )B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
 ORDER BY 1,2

 

쿼리 2

REUSABLE_SPACE_GB : 재사용 가능한 공간
ALLOCATED_GB : 할당되어 사용중인 공간

 

WITH FREE_SZ AS (SELECT TABLESPACE_NAME,
               SUM(F.BYTES)/1048576/1024 FREE_GB
          FROM DBA_FREE_SPACE F
         GROUP BY TABLESPACE_NAME ),
       A AS (SELECT TABLESPACE_NAME ,
               SUM(
                                       CASE
                                         WHEN STATUS = 'EXPIRED' THEN BLOCKS
                                       END)*8/1048576 REUSABLE_SPACE_GB ,
               SUM(
                                       CASE
                                         WHEN STATUS IN ('ACTIVE',
                               'UNEXPIRED') THEN BLOCKS
                                       END)*8/1048576 ALLOCATED_GB
          FROM DBA_UNDO_EXTENTS
         WHERE STATUS IN ('ACTIVE',
                       'EXPIRED',
                       'UNEXPIRED')
         GROUP BY TABLESPACE_NAME ) ,
       UNDO_SZ AS (SELECT TABLESPACE_NAME,
               DF.USER_BYTES/1048576/1024 USER_SZ_GB
          FROM DBA_TABLESPACES TS JOIN DBA_DATA_FILES DF USING (TABLESPACE_NAME)
         WHERE TS.CONTENTS = 'UNDO'
           AND TS.STATUS = 'ONLINE' )
SELECT TABLESPACE_NAME,
       ROUND(USER_SZ_GB,1)      AS USER_SZ_GB,
       ROUND(FREE_GB,1)      AS FREE_GB,
       ROUND(REUSABLE_SPACE_GB,1)      AS REUSABLE_SPACE_GB,
       ROUND(ALLOCATED_GB,1)      AS ALLOCATED_GB ,
       ROUND(FREE_GB + REUSABLE_SPACE_GB + ALLOCATED_GB,1)      AS  TOTAL
  FROM UNDO_SZ JOIN FREE_SZ USING (TABLESPACE_NAME) JOIN A USING (TABLESPACE_NAME);