테이블 정보[편집]

1. 1. 32 테이블 생성일자 보기

  <syntaxhighlight lang="sql"> SELECT SUBSTRB(OBJECT_NAME, 1, 15) AS OBJECT_NAME

    , CREATED
    , LAST_DDL_TIME
    , TIMESTAMP
    , STATUS

 FROM USER_OBJECTS WHERE OBJECT_NAME = UPPER('&테이블명') AND OBJECT_TYPE = 'TABLE'; </syntaxhighlight>

1. 1. 33 테이블의 크기 및 블록 보기

  <syntaxhighlight lang="sql"> SELECT SUBSTR(SEGMENT_NAME, 1, 20), BYTES, BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME = UPPER('&테이블명'); </syntaxhighlight>

1. 1. 34 파티션 테이블의 파티션 범위 보기

  <syntaxhighlight lang="sql"> SELECT SUBSTRB(PARTITION_NAME, 1, 30) AS PARTITION_NAME, SUBSTRB(TABLESPACE_NAME, 1, 30) AS TABLESPACE_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = UPPER('&테이블명'); </syntaxhighlight>

1. 1. 35 CONSTRAINT 보기

  <syntaxhighlight lang="sql"> SELECT DECODE(A.CONSTRAINT_TYPE, 'P', 'Primary Key', 'R', 'Foreign Key', 'C', 'Table Check', 'V', 'View Check', 'U', 'Unique', '?')

           AS "유형"
    , SUBSTRB(A.CONSTRAINT_NAME, 1, 25) AS CONSTRAINT_NAME
    , B.POSITION
    , SUBSTRB(B.COLUMN_NAME, 1, 25) AS COLUMN_NAME

 FROM DBA_CONSTRAINTS A

    , DBA_CONS_COLUMNS B

WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.OWNER = 'E_LUCIS' AND A.TABLE_NAME = UPPER('&테이블명') ORDER BY 1, 2, 3; </syntaxhighlight>

1. 1. 36 특정 테이블의 스키마 구조 확인

  <syntaxhighlight lang="sql"> /*

    보통 토드나 기타 오라클 클라이언트 툴을 이용해서 테이블 구조를 확인 해도 됩니다.
    하지만 수많은 테이블을 전체 보고 싶을 경우 아래 쿼리를 이용하면 한번에 확인이 가능합니다.

• /

--: 관리자용

SELECT OWNER

    , TABLE_NAME
    , COLUMN_NAME
    , PK
    , COLUMN_NAME
    , DATA_TYPE || '( ' || NVL(DATA_TYPE_2, DATA_LENGTH) || ' )' DATA_TYPE
    , NULLABLE
    , COMMENTS

 FROM (SELECT A.OWNER

               , A.TABLE_NAME
               , A.COLUMN_ID
               , B.POSITION PK
               , A.COLUMN_NAME
               , A.DATA_TYPE
               , A.DATA_PRECISION || DECODE(A.DATA_SCALE, NULL, NULL, ',' || A.DATA_SCALE) DATA_TYPE_2
               , A.DATA_LENGTH
               , A.DATA_PRECISION
               , A.DATA_SCALE
               , A.NULLABLE
               , A.COMMENTS
               , ROW_NUMBER() OVER (PARTITION BY A.OWNER, A.TABLE_NAME, A.COLUMN_ID ORDER BY A.COLUMN_ID, B.POSITION) RN
          FROM      (SELECT    COL.OWNER
                          , COL.TABLE_NAME
                          , COL.COLUMN_ID
                          , COL.COLUMN_NAME
                          , COL.DATA_TYPE
                          , COL.DATA_LENGTH
                          , COL.DATA_PRECISION
                          , COL.DATA_SCALE
                          , COL.NULLABLE
                          , COM.COMMENTS
                     FROM      DBA_TAB_COLUMNS COL
                          , DBA_COL_COMMENTS COM
                     WHERE     COL.COLUMN_NAME = COM.COLUMN_NAME
                     AND       COL.OWNER = COM.OWNER
                     AND       COL.TABLE_NAME = COM.TABLE_NAME
                     AND       COM.OWNER =:IN_OWNER
                     AND       COM.TABLE_NAME LIKE:IN_TABLE_NAME || '%') A
               , DBA_CONS_COLUMNS B
          WHERE     B.TABLE_NAME(+) = A.TABLE_NAME
          AND       B.COLUMN_NAME(+) = A.COLUMN_NAME) X

WHERE X.RN = 1 ORDER BY X.TABLE_NAME, X.COLUMN_ID; </syntaxhighlight>

--: 일반 사용자 용

  <syntaxhighlight lang="sql"> SELECT TABLE_NAME , COLUMN_NAME , PK , COLUMN_NAME , DATA_TYPE || '( ' || NVL(DATA_TYPE_2, DATA_LENGTH) || ' )' DATA_TYPE , NULLABLE , COMMENTS

 FROM (SELECT A.TABLE_NAME , A.COLUMN_ID , B.POSITION PK , A.COLUMN_NAME , A.DATA_TYPE 
            , A.DATA_PRECISION || DECODE(A.DATA_SCALE, NULL, NULL, ',' || A.DATA_SCALE) DATA_TYPE_2 
            , A.DATA_LENGTH , A.DATA_PRECISION , A.DATA_SCALE , A.NULLABLE , A.COMMENTS 
            , ROW_NUMBER() OVER (PARTITION BY A.TABLE_NAME, A.COLUMN_ID 
                                     ORDER BY A.COLUMN_ID, B.POSITION) RN 
        FROM (SELECT COL.TABLE_NAME , COL.COLUMN_ID , COL.COLUMN_NAME , COL.DATA_TYPE , COL.DATA_LENGTH 
                   , COL.DATA_PRECISION , COL.DATA_SCALE , COL.NULLABLE , COM.COMMENTS 
                FROM USER_TAB_COLUMNS COL 
                   , USER_COL_COMMENTS COM 
               WHERE COL.COLUMN_NAME = COM.COLUMN_NAME 
                 AND COL.TABLE_NAME = COM.TABLE_NAME 
                 AND COM.TABLE_NAME LIKE:IN_TABLE_NAME || '%') A 
            , USER_CONS_COLUMNS B 
               WHERE B.TABLE_NAME(+) = A.TABLE_NAME AND B.COLUMN_NAME(+) = A.COLUMN_NAME
      ) X 
WHERE X.RN = 1 ORDER BY X.TABLE_NAME, X.COLUMN_ID; 

</syntaxhighlight>

1. 1. 37 사용자별 오브젝트 수

  <syntaxhighlight lang="sql"> SELECT OWNER AS "OWNER"

    , SUM(DECODE(OBJECT_TYPE, 'TABLE', 1, 0)) AS "TABLE"
    , SUM(DECODE(OBJECT_TYPE, 'INDEX', 1, 0)) AS "INDEX"
    , SUM(DECODE(OBJECT_TYPE, 'SYNONYM', 1, 0)) AS "SYNONYMS"
    , SUM(DECODE(OBJECT_TYPE, 'SEQUENCE', 1, 0)) AS "SEQUENCES"
    , SUM(DECODE(OBJECT_TYPE, 'VIEW', 1, 0)) AS "VIEWS"
    , SUM(DECODE(OBJECT_TYPE, 'CLUSTER', 1, 0)) AS "CLUSTERS"
    , SUM(DECODE(OBJECT_TYPE, 'DATABASE LINK', 1, 0)) AS "DBLINKS"
    , SUM(DECODE(OBJECT_TYPE, 'PACKAGE', 1, 0)) AS "PACKAGES"
    , SUM(DECODE(OBJECT_TYPE, 'PACKAGE BODY', 1, 0)) AS "PACKAGE_BODY"
    , SUM(DECODE(OBJECT_TYPE, 'PROCEDURE', 1, 0)) AS "PROCEDURES"
    , SUM(DECODE(OBJECT_TYPE, 'FUNCTION', 1, 0)) AS "FUNCTION"

 FROM DBA_OBJECTS GROUP BY OWNER; </syntaxhighlight>

1. 1. 59 상호 DB간에 컬럼 이름 비교

  <syntaxhighlight lang="sql">

/*

  양쪽 DB에서 사용하는 테이블 중에서 컬럼 이름 다른 항목을 찾는다.

• /

SELECT A.TABLE_NAME

    , A.COLUMN_NAME
    , A.COLUMN_ID
    , A.DATA_TYPE || '(' || A.DATA_LENGTH || ')' DATA_TYPE

 FROM USER_TAB_COLUMNS@LINK_ESTDB A WHERE A.TABLE_NAME =:IN_TABLE_NAME AND NOT EXISTS

           (SELECT    'X'
            FROM      USER_TAB_COLUMNS B
            WHERE     B.TABLE_NAME = A.TABLE_NAME
            AND       B.COLUMN_NAME = A.COLUMN_NAME);

</syntaxhighlight>

  <syntaxhighlight lang="sql"> SELECT A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_ID FROM USER_TAB_COLUMNS@LINK_ESTDB A WHERE A.TABLE_NAME =:IN_TABLE_NAME MINUS SELECT A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_ID FROM USER_TAB_COLUMNS A WHERE A.TABLE_NAME =:IN_TABLE_NAME </syntaxhighlight>