[Oracle] 시스템 테이블 정리

[Oracle] 시스템 테이블 정리

안녕하세요. 갓대희 입니다. 이번 포스팅은 [ 오라클 시스템 테이블 ] 입니다. : ) 

오라클 System 테이블

테이블명	설명
USER_OBJECTS	모든 오브젝트에 대한 정보를 확인할 수 있다. 유형, 작성시간, 오브젝트에 사용된 최종 DDL 명령, alter, grant 및 revoke 등
USER_USERS	사용자 정보.
USER_TAB_PRIVS	테이블 권한정보 테이블의 접송 계정별 접근 권한을 알 수 있다.
USER_SYS_PRIVS	시스템 권한정보 사용자에게 주어진 권한을 알 수 있다.
USER_VIEWS	뷰 정보 VIEW 테이블의 스크립트의 길이와 내용을 알 수 있다.
USER_TABLES	테이블 정보 테이블의 모든 정보를 알 수 있다. (TABLESPACE_NAME/ NUM_ROWS / BLOCKS 등)
USER_TAB_COLUMNS	컬럼 정보 테이블의 열(COLUMN) 정보를 알 수 있다. (COLUMN_NAME / DATA_TYPE / NULLABLE 등)
USER_TAB_COMMENTS	테이블/뷰에 대한 주석 정보
USER_COL_COMMENTS	열에 대한 주석정보
USER_CONS_COLUMNS	제약조건(CONSTRAINT)을 가진 열에 대한 정보를 조회한다. PK나, FK 목록을 한번에 보고 싶을 때 활용 가능.
USER_FREE_SPACE	여유영역 현재 여유로 표시된 영역이 얼마인지에 대한 정보
USER_SEGMENTS USER_EXTENTS	세그먼츠 관련 정보
USER_SNAPSHOTS	스탭샷
USER_ERRORS	코드 오류
USER_RESOURCE_LIMITS	자원 제한량
USER_SYNONYMS	동의어 정보
USER_SEQUENCES	시퀀스 정보
USER_CONSTRAINTS	제약조건 정보
USER_INDEXES	인덱스 정보
USER_IND_COLUMNS	인덱스 열  정보
USER_TRIGGERS	트리거에 정보

이정도 선에서 벗어난다면 거의 사용해본적이 없거나 할 일이 없는 테이블이 아닐까 싶다.

추가로

이 시스템 테이블을 활용하여 많은 사용 예가 있는데 이번엔 한가지 

해당 user의 테이블 정보를 확인 할 수 있는 방법을 살펴 보았다.

USER_TAB_COLUMNS	컬럼 정보 테이블의 열(COLUMN) 정보를 알 수 있다. (COLUMN_NAME / DATA_TYPE / NULLABLE 등)
USER_TAB_COMMENTS	테이블/뷰에 대한 주석 정보
USER_COL_COMMENTS	열에 대한 주석정보

다음 System테이블중 3가지 테이블을 활용하여 테이블 정보를 한눈에 볼 수 있는 테이블을 만들 수 있다.

/***************************************

***** 1. 테이블정보 넣을 테이블 생성 *****

***************************************/

CREATE TABLE TABLE_INFO (

    OWNER VARCHAR(15) NOT NULL

    , TABLE_NAME VARCHAR(30) NOT NULL

    , TABLE_COMMENTS VARCHAR(200)

    , COLUMN_NAME VARCHAR(30) NOT NULL

    , COLUMN_COMMENTS VARCHAR(200)

    , COLUMN_ID NUMBER

    , DATA_TYPE VARCHAR(20) NOT NULL

    , NULLABLE CHAR(1) NOT NULL

    , TABLE_TYPE VARCHAR(20)

);

COMMENT ON COLUMN TABLE_INFO.OWNER IS 'OWNER';

COMMENT ON COLUMN TABLE_INFO.TABLE_NAME IS '테이블명';

COMMENT ON COLUMN TABLE_INFO.TABLE_COMMENTS IS '테이블설명';

COMMENT ON COLUMN TABLE_INFO.COLUMN_NAME IS '컬럼명';

COMMENT ON COLUMN TABLE_INFO.COLUMN_COMMENTS IS '컬럼설명';

COMMENT ON COLUMN TABLE_INFO.COLUMN_ID IS '컬럼ID';

COMMENT ON COLUMN TABLE_INFO.DATA_TYPE IS '데이터타입';

COMMENT ON COLUMN TABLE_INFO.NULLABLE IS 'NULL허용여부';

COMMENT ON COLUMN TABLE_INFO.TABLE_TYPE IS '테이블형태';

ALTER TABLE TABLE_INFO ADD (CONSTRAINT PK_INFO PRIMARY KEY (OWNER, TABLE_NAME,COLUMN_NAME));

COMMIT;

/******************************

***** 2. 테이블정보 넣기   *****

******************************/

INSERT INTO TABLE_INFO

SELECT  TB_COM.OWNER

        , TB_COM.TABLE_NAME

        , TB_COM.TCOMMENTS

        , TB_COM.COLUMN_NAME

        , TB_COM.CCOMMENTS

        , TB_COL.COLUMN_ID

        , TB_COL.DATA_TYPE

        , TB_COL.NULLABLE

        , TB_COM.TABLE_TYPE

FROM    (

            SELECT  USER AS OWNER

                    , A.TABLE_NAME AS TABLE_NAME

                    , A.COMMENTS AS TCOMMENTS

                    , B.COLUMN_NAME AS COLUMN_NAME

                    , B.COMMENTS AS CCOMMENTS

                    , A.TABLE_TYPE AS TABLE_TYPE

            FROM    USER_TAB_COMMENTS A

                    , USER_COL_COMMENTS B

            WHERE   A.TABLE_NAME = B.TABLE_NAME(+)

        ) TB_COM,

        (

            SELECT  TABLE_NAME

                    , COLUMN_NAME

                    , NULLABLE

                    , COLUMN_ID

                    ,(

                        CASE

                            WHEN DATA_PRECISION IS NULL THEN DATA_TYPE|| '(' || DATA_LENGTH || ')'

                            WHEN DATA_SCALE = 0 THEN DATA_TYPE || '(' || DATA_PRECISION|| ')'

                            ELSE DATA_TYPE || '(' || DATA_PRECISION || '.' || DATA_SCALE || ')'

                        END

                    ) AS DATA_TYPE

            FROM    USER_TAB_COLUMNS

        ) TB_COL

WHERE   TB_COM.TABLE_NAME = TB_COL.TABLE_NAME

AND     TB_COM.COLUMN_NAME = TB_COL.COLUMN_NAME;

COMMIT;

/**********************************************

***** 3.테이블 생성 하기 싫고 화면에 표현만 하고싶을때

***********************************************/

/* 테이블명, 컬럼명, 컬럼ID, PK여부, NULL 여부, 데이터 타입, 디폴트 값, 코멘트(컬럼의 한글 명)*/

SELECT   B.TABLE_NAME, B.COLUMN_NAME, B.COLUMN_ID, 

         (  SELECT Y.CONSTRAINT_TYPE

              FROM USER_IND_COLUMNS X, USER_CONSTRAINTS Y

             WHERE X.INDEX_NAME = Y.CONSTRAINT_NAME

               AND Y.TABLE_NAME = A.TABLE_NAME

               AND X.COLUMN_NAME = A.COLUMN_NAME 

               AND Y.CONSTRAINT_TYPE = 'P' --PK 구분입니당

         ) AS PK, 

         B.NULLABLE, 

         DECODE(B.DATA_TYPE, 

                'DATE', B.DATA_TYPE, 

                'NUMBER', B.DATA_TYPE||' ('||B.DATA_PRECISION||','||B.DATA_SCALE||')', --숫자형인 경우, 자리수와, 소수점까지

                B.DATA_TYPE||' ('||B.DATA_LENGTH||' Byte)'

         ) AS DATA_TYPE,

         B.DATA_DEFAULT, 

         A.COMMENTS

    FROM USER_COL_COMMENTS A, USER_TAB_COLUMNS B

   WHERE A.TABLE_NAME = B.TABLE_NAME    --테이블명

     AND A.COLUMN_NAME = B.COLUMN_NAME  --컬럼명

     AND B.TABLE_NAME LIKE 'T_%'   --조회하고자 하는 테이블명

   --AND B.TABLE_NAME = '--테이블명--'   --조회하고자 하는 테이블명

ORDER BY B.TABLE_NAME, B.COLUMN_ID;