[쿼리] 오라클 현행 물리 테이블 명세서 추출 쿼리

오라클 데이터베이스의 물리 테이블로부터 테이블 명세를 추출할 때 사용하는 쿼리입니다.

 

/*
※ DB 스키마 조회
1. DBA권한을 가진 스키마로 DB 접속 후
2. OBJECT 관리 스키마 목록을 "(<소유자명>)"에 열거한 후 SQL을 수행한다.
   예) OWNER IN ('AAA', 'BBB', 'CCC')
3. 쿼리 결과를 CSV 파일 또는 Excel 파일로 저장 한다.
   저장 파일명은 시스템명_쿼리명.csv
   예) OOO_01_컬럼목록.csv
*/

/* 
쿼리명 : 01_컬럼목록
설  명 : Oracle Dictionary에서 컬럼의 Comment 와 Type, Null 여부를 조회.
*/
SELECT
      A.OWNER
    , A.TABLE_NAME
    , A.COLUMN_NAME
    , A.COLUMN_ID AS COL_ORDER
    , CASE WHEN A.DATA_TYPE IN ('VARCHAR', 'VARCHAR2', 'CHAR') THEN
                          A.DATA_TYPE || '(' || A.DATA_LENGTH || ')'
           WHEN A.DATA_TYPE = 'NUMBER' THEN
                          A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' || DECODE(A.DATA_SCALE,0,'0',A.DATA_SCALE) || ')'
           ELSE A.DATA_TYPE
      END AS FULL_DATA_TYPE
    , A.DATA_TYPE
    , A.DATA_LENGTH
    , A.DATA_PRECISION
    , A.DATA_SCALE
    , A.NULLABLE
    , B.COMMENTS
    , A.DATA_DEFAULT
 FROM DBA_TAB_COLUMNS A
    , DBA_COL_COMMENTS B
 WHERE  A.OWNER IN (<소유자명>)
 AND  A.OWNER       = B.OWNER(+)
 AND  A.TABLE_NAME  = B.TABLE_NAME(+)
 AND  A.COLUMN_NAME = B.COLUMN_NAME(+);

/* 
쿼리명 : 02_제약사항
설  명 : Oracle Dictionary에서 PK를 포함한 Constraint 컬럼 조회
Constraint Type
P : Primary
U : Unique Index
C : Check
R : Relation Ship (Forgin Key)
*/
SELECT  A.OWNER
    , A.TABLE_NAME
    , A.CONSTRAINT_TYPE
    , A.CONSTRAINT_NAME
    , DECODE(A.STATUS, 'ENABLED', 'Y', NULL) ENABLED
    , B.COLUMN_NAME
    , B.POSITION
    , A.R_OWNER
    , A.R_CONSTRAINT_NAME
 FROM  DBA_CONSTRAINTS A
    , DBA_CONS_COLUMNS B
 WHERE  A.OWNER IN (<소유자명>)
 AND  A.CONSTRAINT_TYPE IN ('P','U','R')
 AND  A.OWNER  = B.OWNER
 AND  A.CONSTRAINT_NAME = B.CONSTRAINT_NAME;

/* 
쿼리명 : 03_인덱스
설  명 : Oracle Dictionary에서 INDEX 컬럼을 조회
*/
SELECT  A.INDEX_OWNER
    , A.INDEX_NAME
    , A.TABLE_OWNER
    , A.TABLE_NAME
    , A.COLUMN_NAME
    , A.COLUMN_POSITION
    , A.COLUMN_LENGTH
    , A.CHAR_LENGTH
    , A.DESCEND
    , B.CONSTRAINT_TYPE
 FROM DBA_IND_COLUMNS A
    , DBA_CONSTRAINTS B
 WHERE  A.INDEX_OWNER IN (<소유자명>)
 AND  A.INDEX_OWNER = B.OWNER (+)
 AND  A.INDEX_NAME  = B.CONSTRAINT_NAME (+);

/* 
쿼리명 : 04_테이블목록
설  명 : Oracle Dictionary에서 테이블 목록 및 테이블 코멘트를 조회
*/
SELECT  A.*
       ,B.COMMENTS
  FROM  DBA_TABLES A
       ,DBA_TAB_COMMENTS B
 WHERE A.OWNER IN (<소유자명>)
-- WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
 AND A.OWNER = B.OWNER (+)
 AND A.TABLE_NAME  = B.TABLE_NAME (+)
 AND B.COMMENTS (+) IS NOT NULL;
 
/* 
쿼리명 : 05_FK키
설  명 : Oracle Dictionary에서 FK Constraint 참조 테이블 및 컬럼 조회
*/
SELECT  A.CONSTRAINT_NAME
       ,A.OWNER
       ,A.TABLE_NAME
       ,A.PK_OWNER
       ,A.PK_TABLE
       ,A.CONSTRAINT_TYPE
       ,MAX(CASE WHEN B.POSITION IS NULL OR B.POSITION=1 THEN B.COLUMN_NAME END) COLUMN01
       ,MAX(CASE WHEN B.POSITION= 2 THEN B.COLUMN_NAME END) COLUMN02
       ,MAX(CASE WHEN B.POSITION= 3 THEN B.COLUMN_NAME END) COLUMN03
       ,MAX(CASE WHEN B.POSITION= 4 THEN B.COLUMN_NAME END) COLUMN04
       ,MAX(CASE WHEN B.POSITION= 5 THEN B.COLUMN_NAME END) COLUMN05
       ,MAX(CASE WHEN B.POSITION= 6 THEN B.COLUMN_NAME END) COLUMN06
       ,MAX(CASE WHEN B.POSITION= 7 THEN B.COLUMN_NAME END) COLUMN07
       ,MAX(CASE WHEN B.POSITION= 8 THEN B.COLUMN_NAME END) COLUMN08
       ,MAX(CASE WHEN B.POSITION= 9 THEN B.COLUMN_NAME END) COLUMN09
       ,MAX(CASE WHEN B.POSITION=10 THEN B.COLUMN_NAME END) COLUMN10
FROM (
      SELECT  A.CONSTRAINT_NAME
             ,A.OWNER      AS OWNER
             ,A.TABLE_NAME AS TABLE_NAME
             ,A.R_OWNER    AS PK_OWNER
             ,B.TABLE_NAME AS PK_TABLE
             ,A.CONSTRAINT_TYPE
             ,DECODE(A.STATUS, 'ENABLED', 'Y', NULL) ENABLED
        FROM  DBA_CONSTRAINTS A
             ,DBA_CONSTRAINTS B
       WHERE A.OWNER IN (<소유자명>)
       AND A.CONSTRAINT_TYPE IN ('P','R')
       AND A.R_OWNER = B.OWNER(+)
       AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME(+)
     ) A
     , ALL_CONS_COLUMNS B
WHERE A.OWNER = B.OWNER
  AND A.TABLE_NAME = B.TABLE_NAME
GROUP BY A.OWNER, A.TABLE_NAME, A.PK_OWNER, A.PK_TABLE, A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE;

 
/* 
쿼리명 : 06_레코드건수
설  명 : 테이블별 레코드건수 및 사이즈를 조회
※ ANALYZE 를 수행한 테이블만 용량산정이 가능함
*/
WITH
TM_TB_SIZE AS (
SELECT  SEGMENT_NAME AS TABLE_NAME
    , BYTES
    , (BYTES/1024)/1024 AS TABLE_SIZE
 FROM  DBA_SEGMENTS
 WHERE  SEGMENT_TYPE = 'TABLE'
 AND  OWNER IN (<소유자명>)
)
SELECT  A.TABLE_NAME
    , A.TABLESPACE_NAME
    , A.NUM_ROWS
    , A.BLOCKS
    , A.LAST_ANALYZED
    , A.PARTITIONED
    , B.BYTES      AS "TABLE_SIZE(BYTE)"
    , B.TABLE_SIZE AS "TABLE_SIZE(MB)"
 FROM  DBA_TABLES A
    , TM_TB_SIZE B
 WHERE  A.OWNER IN (<소유자명>)
 AND    A.TABLE_NAME = B.TABLE_NAME (+);

/*
쿼리명 : 07_테이블스페이스용량
설  명 : 대략적인 테이블스페이스별 할당량, 사용량을 조회 한다.
*/
SELECT A.TABLESPACE_NAME
   , ROUND(A.BYTES / 1024 / 1024) AS "MB ALLOCATED"
   , ROUND((A.BYTES-NVL(B.BYTES, 0)) / 1024 / 1024) AS "MB USED"
   , NVL(ROUND(B.BYTES / 1024 / 1024), 0) AS "MB FREE"
   , ROUND(((A.BYTES-NVL(B.BYTES, 0))/A.BYTES)*100,2) AS "PCT USED"
   , ROUND((1-((A.BYTES-NVL(B.BYTES,0))/A.BYTES))*100,2) AS "PCT FREE"
FROM   (  SELECT TABLESPACE_NAME
             , SUM(BYTES) BYTES
          FROM SYS.DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME
     ) A,
     (  SELECT TABLESPACE_NAME
             , SUM(BYTES) BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME
     ) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+);

/*
쿼리명 : 08_오브젝트용량
설  명 : 대략적인 테이블스페이스내 오브젝트의 사용량을 조회 한다.
*/
SELECT S.OWNER
   , SUBSTR(S.SEGMENT_NAME, 1, 30) AS "TABLE NAME"
   , ROUND(SUM(S.BYTES) / 1024 / 1024, 2) AS "MB ALLOCATED"
   , CASE WHEN T.BLOCKS > 0 THEN
            ROUND(SUM(S.BYTES) / 1024 / 1024 * TO_NUMBER(DECODE(T.BLOCKS, NULL, NULL, (T.BLOCKS / (T.BLOCKS + T.EMPTY_BLOCKS)))), 2)
          ELSE 0
     END AS "MB USED"
   , CASE WHEN T.BLOCKS > 0 THEN
            TO_NUMBER(DECODE(T.BLOCKS,NULL, NULL, ROUND(100 *(T.BLOCKS / (T.BLOCKS + T.EMPTY_BLOCKS)), 2)))
          ELSE 0
     END AS "PCT FULL"
   , S.TABLESPACE_NAME
 FROM SYS.DBA_SEGMENTS S
   ,  SYS.DBA_TABLES T
 WHERE T.OWNER = S.OWNER
 AND   T.TABLE_NAME = S.SEGMENT_NAME
 AND   T.TABLESPACE_NAME = S.TABLESPACE_NAME
 AND   S.OWNER IN (<소유자명>)
 GROUP BY S.TABLESPACE_NAME, S.OWNER, S.SEGMENT_NAME, S.SEGMENT_TYPE, T.BLOCKS, T.EMPTY_BLOCKS;

 

 

요즘은 모델링 툴 활용이 많아져서 논리/물리 모델 명세를 보유하고 계신 경우가 많겠지만,

간혹 사용할 일이 생기더라구요~ ^^