[Oracle] 컬럼 코멘트, 인덱스, PARTITION, 테이블 스페이스 조회

업무와 상관없이 두고두고 쓰는 몇가지 쿼리들.. 저장해두고 쓰자

 

1. 테이블, 컬럼 코멘트 조회

SELECT A.OWNER, A.TABLE_NAME, B,COMMENTS, A,COLUMN_NAME
     , ( SELECT X.COMMENTS
           FROM ALL_COL_COMMENTS X
          WHERE X.OWNER = A.OWNER
            AND X.TABLE_NAME = A.TABLE_NAME
            AND X.COLUMN_NAME = A.COLUMN_NAME
            -- AND X.COMMENTS LIKE '%%' -- 컬럼 코멘트
       ) COL_COMMENTS
     , A.DATA_TYPE
     , A.DATA_LENGTH
     , A.DATA_PRECISION
     , A.DATA_SCALE
  FROM ALL_TAB_COLS A, ALL_TAB_COMMENTS B
 WHERE 1=1
   AND A.TABLE_NAME LIKE '%테이블 명%' -- 테이블 명
   AND B.OWNER = A.OWNER
   AND B.TABLE_NAME = A.TABLE_NAME
   -- AND A.COUMN_NAME LIKE '%%' -- 컬럼 명
 ORDER BY A.TABLE_NAME, A.COLUMN_ID
 ;

 

2. 인덱스 조회 

SELECT A.*
     , B.COLUMN_EXPRESSION
  FROM ALL_IND_COLUMNS A
     , ALL_IND_EXPRESSIONS B
 WHERE A.TABLE_NAME = '테이블 명' -- 테이블 명
   AND B.TABLE_NAME(+) = A.TABLE_NAME
   AND B.INDEX_NAME(+) = A.INDEX_NAME
   AND B.COLUMN_POSITION(+) = A.COLUMN_POSITION
 ORDER BY 2, 6;

 

3. PARTITION 조회

SELECT A.TABLE_OWNER
     , A.TABLE_NAME
     , B.PARTITIONING_TYPE
     , B.SUBPARTITIONING_TYPE
     , B.PARTITION_COUNT
     , (SELECT LISTAGG(IX.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY IX.COLUMN_POSITION)
          FROM ALL_PART_KEY_COLUMNS IX
         WHERE IX.NAME = A.TABLE_NAME) COLUMN_NAME
     , A.HIGH_VALUE
     , A.SEGMENT_CREATED
     , A.PARTITION_NAME
  FROM ALL_TAB_PARTITIONS A
     , ALL_PART_TABLES B
 WHERE 1=1
   AND A.TABLE_NAME = '테이블 명' -- 테이블 명
   AND B.TABLE_NAME = A.TABLE_NAME
;

 

4. 테이블 스페이스

SELECT * FROM DBA_TABLESPACES;
 
SELECT SUBSTR(A.TABLESPACE_NAME, 1, 30) TABLESPACE
     , ROUND(SUM(A.TOTAL1)/1024/1024,1) TOTALMB
     , ROUND(SUM(A.TOTAL1)/1024/1024,1) - ROUND(SUM(A.SUM1)/1024/1024,1) USEDMB
     , ROUND(SUM(A.SUM1)/1024/1024,1) FREEMB
     , ROUND((ROUND(SUM(A.TOTAL1)/1024/1024.1) - ROUND(SUM(A.SUM1)/1024/1024.1))/(ROUND(SUM(A.TOTAL1)/1024/1024,1))*100,2) "UESD%"
  FROM (
        SELECT TABLESPACE_NAME, 0 TOTAL1, SUM(BYTES) SUM1, MAX(BYTES) MAXB, COUNT(BYTES) CNT
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME
         UNION
        SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL1, 0, 0, 0
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME
       ) A
 GROUP BY A.TABLESPACE_NAME
 ORDER BY TABLESPACE
;

 

5. DB 락 확인

SELECT * FROM V$LOCKED_OBJECT;
 
SELECT A.*, B.OBJECT_NAME, B.OWNER, B.OBJECT_TYPE
  FROM V$LOCKED_OBJECT A
     , DBA_OBJECTS B
 WHERE A.OBJECT_ID = B.OBJECT_ID;
 
SELECT DISTINCT X.SESSION_ID, A.SERIAL#, D.OBJECT_NAME, A.MACHINE, A.TERMINAL
     , A.PROGRAM, B.ADDRESS, B.PIECE, B.SQL_TEXT
  FROM V$LOCKED_OBJECT X, V$SESSION A, V$SQLTEXT B, DBA_OBEJECTS D
 WHERE X.SESSION_ID = A.SID
   AND X.OBJECT_ID = D.OBJECT_ID
   AND A.SQL_ADDRESS = B.ADDRESS
 ORDER BY B.ADDRESS, B.PIECE;