테이블 스페이스[편집]

1. 테이블스페이스별 파일 목록을 보기

SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS "테이블스페이스"
     ,  SUBSTRB(FILE_NAME, 1, 50) AS "파일명"
     ,  TO_CHAR(BLOCKS, '999,999,990') AS "블럭수"
     ,  TO_CHAR(BYTES, '99,999,999') AS "크기"
  FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME, FILE_NAME;

1. 테이블스페이스별 정보 보기

SELECT A.TABLESPACE_NAME AS "TABLESPACE"
     , A.INITIAL_EXTENT / 1024 AS "INIT(K)"
     , A.NEXT_EXTENT / 1024 AS "NEXT(K)"
     , A.MIN_EXTENTS AS "MIN"
     , A.MAX_EXTENTS AS "MAX"
     , A.PCT_INCREASE AS "PCT_INC(%)"
     , B.FILE_NAME AS "FILE_NAME"
     , B.BLOCKS * C.VALUE / 1024 / 1024 AS "SIZE(M)"
     , B.STATUS AS "STATUS"
  FROM DBA_TABLESPACES A
     ,  DBA_DATA_FILES B
     ,  V$PARAMETER C
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND C.NAME = 'db_block_size' ORDER BY 1, 2;

1. 테이블스페이스별 사용하는 파일의 크기 합 보기

SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS TABLESPACE

     , TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES
     , TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS

  FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME UNION ALL SELECT '총계', TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES, TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS FROM DBA_DATA_FILES;

1. 테이블스페이스별 디스크 사용량 보기

SELECT A.TABLESPACE_NAME AS "TABLESPACE"

     , A.INIT AS "INIT(K)"
     , A.NEXT AS "NEXT(K)"
     , A.MIN AS "MIN"
     , A.MAX AS "MAX"
     , A.PCT_INC AS "PCT_INC(%)"
     , TO_CHAR(B.TOTAL, '999,999,999,990') AS "총량(바이트)"
     , TO_CHAR(C.FREE, '999,999,999,990') AS "남은량(바이트)"
     , TO_CHAR(B.BLOCKS, '9,999,990') AS "총블럭"
     , TO_CHAR(D.BLOCKS, '9,999,990') AS "사용블럭"
     , TO_CHAR(100 * NVL(D.BLOCKS, 0) / B.BLOCKS, '999.99') AS "사용율%"

  FROM (SELECT TABLESPACE_NAME

                , INITIAL_EXTENT / 1024 AS INIT
                , NEXT_EXTENT / 1024 AS NEXT
                , MIN_EXTENTS AS MIN
                , MAX_EXTENTS AS MAX
                , PCT_INCREASE AS PCT_INC
           FROM      DBA_TABLESPACES) A
     , (SELECT    TABLESPACE_NAME, SUM(BYTES) AS TOTAL, SUM(BLOCKS) AS BLOCKS
           FROM      DBA_DATA_FILES
           GROUP BY  TABLESPACE_NAME) B
     , (SELECT    TABLESPACE_NAME, SUM(BYTES) AS FREE
           FROM      DBA_FREE_SPACE
           GROUP BY  TABLESPACE_NAME) C
     , (SELECT    TABLESPACE_NAME, SUM(BLOCKS) AS BLOCKS
           FROM      DBA_EXTENTS
           GROUP BY  TABLESPACE_NAME) D

 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME = D.TABLESPACE_NAME(+) ORDER BY A.TABLESPACE_NAME;

1. 테이블스페이스의 테이블 명 보기

SELECT TABLESPACE_NAME, TABLE_NAME 
  FROM USER_TABLES 
 WHERE TABLESPACE_NAME = UPPER('&테이블스페이스명') 
 ORDER BY TABLESPACE_NAME, TABLE_NAME;

1. 공간의 90% 이상을 사용하고 있는 Tablespace

SELECT X.TABLESPACE_NAME
     , TOTAL_SIZE / 1024 / 1024 TOTAL_SIZE
     , USED_SIZE / 1024 / 1024 USED_SIZE
     , (ROUND(USED_SIZE / TOTAL_SIZE, 2)) * 100 USED_RATIO
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL_SIZE
           FROM      DBA_DATA_FILES
           GROUP BY  TABLESPACE_NAME) X
     , (SELECT    TABLESPACE_NAME, SUM(BYTES) USED_SIZE
           FROM      DBA_EXTENTS
           GROUP BY  TABLESPACE_NAME) Y

 WHERE X.TABLESPACE_NAME = Y.TABLESPACE_NAME(+) AND Y.USED_SIZE > .9 * X.TOTAL_SIZE;

1. Object별 테이블스페이스 및 데이터파일

SELECT    DISTINCT E.SEGMENT_NAME, E.TABLESPACE_NAME, F.FILE_NAME
  FROM DBA_EXTENTS E
     , DBA_DATA_FILES F
 WHERE E.FILE_ID = F.FILE_ID 
   AND E.SEGMENT_TYPE = 'TABLE' 
   AND E.TABLESPACE_NAME NOT IN ('SYSTEM', 'TOOLS');

1. Tablespace별 Table, Index 개수

SELECT OWNER

     , TABLESPACE_NAME
     , SUM(DECODE(SEGMENT_TYPE, 'TABLE', 1, 0))
     , SUM(DECODE(SEGMENT_TYPE, 'INDEX', 1, 0))

  FROM DBA_SEGMENTS WHERE SEGMENT_TYPE IN ('TABLE', 'INDEX') GROUP BY OWNER, TABLESPACE_NAME;

1. 위치별 space를 아는 방법

SELECT SUBSTRB(A.FILE_NAME, 1, 40) AS FILE_NAME
     , A.FILE_ID
     , B.FREE_BYTES / 1024 AS FREE_BYTES
     , B.MAX_BYTES / 1024 AS MAX_BYTES
  FROM DBA_DATA_FILES A
     , (SELECT FILE_ID, SUM(BYTES) AS FREE_BYTES, MAX(BYTES) AS MAX_BYTES
          FROM DBA_FREE_SPACE
         GROUP BY  FILE_ID) B

 WHERE A.FILE_ID = B.FILE_ID AND A.TABLESPACE_NAME = UPPER('&테이블스페이스명') ORDER BY A.FILE_NAME;

1. 현재 Extension 횟수가 MaxExtents의 80% 이상인 경우

SELECT TABLESPACE_NAME
     , OWNER
     , SEGMENT_NAME
     , SEGMENT_TYPE
     , EXTENTS
     , MAX_EXTENTS
  FROM SYS.DBA_SEGMENTS S 
 WHERE EXTENTS / MAX_EXTENTS > .8 AND MAX_EXTENTS > 0 
 ORDER BY TABLESPACE_NAME, OWNER, SEGMENT_NAME;

1. 테이블의 익스텐트 정보 조회

/*

    오라클에서 스토리지 구조는 아래와 같다.
    테이블 스페이스 -> 세그먼트 -> 익스텐트 -> 블록 -> OS 범위 -> 데이터 파일 -> 운영체제 블록
     
    세그먼트의 이름,
    해당 세그먼트의 최대 익스텐트 개수,
    익스텐트 아이디
    해당 세그먼트의 최대 익스텐트 개수 - 최대 익스텐트 아이디
     
    딕셔너리 관리 테이블스페이스로 생성한 것으로 조회를 한다.
     

*/ 

SELECT B.SEGMENT_NAME

     , B.MAX_EXTENTS
     , MAX(C.EXTENT_ID) AS EXTENT_ID
     , B.MAX_EXTENTS - MAX(C.EXTENT_ID) AS DIFF

  FROM USER_TABLESPACES A

     , USER_SEGMENTS B
     , USER_EXTENTS C

 WHERE A.EXTENT_MANAGEMENT = 'DICTIONARY' AND B.TABLESPACE_NAME = A.TABLESPACE_NAME AND C.SEGMENT_NAME = B.SEGMENT_NAME GROUP BY B.SEGMENT_NAME, B.MAX_EXTENTS HAVING B.MAX_EXTENTS - MAX(C.EXTENT_ID) <= 50 ORDER BY B.MAX_EXTENTS - MAX(C.EXTENT_ID);

1. ROLLBACK SEGMENT의 사용상황 보기

--: EXTENTS = 현재 할당된 EXTENT의 수 --: EXTENDS = 마지막 트랜잭션에 의해 할당된 EXTENT의 수

SELECT SUBSTRB(A.SEGMENT_NAME, 1, 10) AS SEGMENT_NAME

     , SUBSTRB(A.TABLESPACE_NAME, 1, 10) AS TABLESPACE_NAME
     , TO_CHAR(A.SEGMENT_ID, '99,999') AS SEG_ID
     , TO_CHAR(A.MAX_EXTENTS, '999,999') AS MAX_EXT
     , TO_CHAR(B.EXTENTS, '999,999') AS EXTENTS
     , TO_CHAR(B.EXTENDS, '999,999') AS EXTENDS
     , TO_CHAR((A.INITIAL_EXTENT + (B.EXTENTS - 1) * A.NEXT_EXTENT) / 1000000, '9,999.999') AS "ALLOC(MB)"
     , TO_CHAR(XACTS, '9,999') AS XACTS

  FROM DBA_ROLLBACK_SEGS A

     , V$ROLLSTAT B

 WHERE A.SEGMENT_ID = B.USN(+) ORDER BY 1;