본문 바로가기
4. Database/4_3 Oracle 기본

[Oracle] 시스템 테이블 정리

by 갓대희 2018. 6. 14.
반응형


[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;


반응형

댓글0