Oracle 에서 테이블과 리소스의 존재 여부를 알아보는 쿼리

개발환경 : Oracle 11g, window 7 32bit

 

모든 오라클에 있는 정보를 알수 있는 테이블들은 USER_* 로 시작한다.

아래는 이런 정보들을 알수 있는 테이블에 대한 정보이다

그림은 user_tables 을 조회했을때의 내역이다.

 

1. USER_TABLES : 테이블정보

2. USER_TAB_COLUMNS : 컬럼정보

3. USER_OBJECTS : 모든 오브젝트의 정보를 알려줌

4. USER_VIEWS : 뷰에 대한 정보

5. USER_SYNONYMS : 동의의 정보

6. USER_SEQUENCES : 시퀀스 정보

7. USER_CONSTRAINTS : 제약조건에 대한 정보

8. USER_CONS_COLUMNS : 제약조건에 대한 컬럼정보

9. USER_TAB_COMMENTS : 테이블/뷰에 대한 주석

10. USER_COL_COMMENTS : 컬럼에 대한 주석

11. USER_INDEXES : 인덱스에 대한 정보

12. USER_IND_COLUMNS : 인덱스 컬럼에 대한 정보

13. USER_CLUSTERS : 클러스터에 대한 정보

14. USER_DB_LINKS : 데이터베이스 링크 정보

15. USER_TRIGGERS : 트리거 정보

16. USER_SOURCE : 프로시저, 함수, 패키지 정보

17. USER_ERRORS : 코드 에러에 대한 정보

18. USER_TABLESPACES : 테이블 스페이스 정보

19. USER_USERS : 사용자에 대한 정보

20. USER_TAB_PRIVS : 테이블 권한에 대한 정보

21. USER_COL_PRIVS : 테이블열 권한에 대한 정보

22. USER_SYS_PRIVS : 시스템 권한에 대한 정보

 

아래 예제는 위에서 소개한 테이블 정보를 조합에서 보기 편리하게 만든 쿼리이다.

 

테이블에 대한 정보를 상세하게 보여준다.

 

SELECT   COL.TABLE_NAME AS TNAME,

           TCOM.COMMENTS AS TCMT,

           COL.COLUMN_NAME AS COL_NM,

           CCOM.COMMENTS AS COL_CMT,

           COL.DATA_TYPE AS TYPE_CD,

           DECODE (COL.DATA_TYPE,

                   'NUMBER', COL.DATA_PRECISION || '.' || COL.DATA_SCALE,

                   COL.DATA_LENGTH)

              AS LENGTH,

           COL.NULLABLE AS NULL_YN,

           COL.DATA_DEFAULT AS D_DEFAULT,

           COL.NUM_DISTINCT AS NUM_DISTINCT

    FROM   USER_TAB_COLUMNS COL, USER_TAB_COMMENTS TCOM, USER_COL_COMMENTS CCOM

   WHERE       COL.TABLE_NAME = TCOM.TABLE_NAME

           AND COL.TABLE_NAME = CCOM.TABLE_NAME

           AND COL.COLUMN_NAME = CCOM.COLUMN_NAME

           AND COL.TABLE_NAME = 'COUNTRIES'

ORDER BY   COL.TABLE_NAME, COL.COLUMN_ID

테이블 정보와 제약조건을 알려주는 정보테이블을 조합해서 PK 정보를 조회한다.

 

SELECT   COL.TABLE_NAME AS TNAME,

           TCOM.COMMENTS AS TCMT,

           COL.COLUMN_NAME AS COL_NM,

           CCOM.COMMENTS AS COL_CMT,

           COL.COLUMN_ID ID,

           PKCOL.COLUMN_POSITION AS PK,

           COL.DATA_TYPE AS TYPE_CD,

           DECODE (COL.DATA_TYPE,

                   'NUMBER', COL.DATA_PRECISION || '.' || COL.DATA_SCALE,

                   COL.DATA_LENGTH)

              AS LENGTH,

           COL.NULLABLE AS NULL_YN,

           COL.DATA_DEFAULT AS D_DEFAULT,

           COL.NUM_DISTINCT AS NUM_DISTINCT

    FROM   USER_TAB_COLUMNS COL,

           USER_TAB_COMMENTS TCOM,

           USER_COL_COMMENTS CCOM,

           (SELECT   AA.TABLE_NAME,

                     AA.INDEX_NAME,

                     AA.COLUMN_NAME,

                     AA.COLUMN_POSITION

              FROM   USER_IND_COLUMNS AA, USER_CONSTRAINTS BB

             WHERE       BB.CONSTRAINT_TYPE = 'P'

                     AND AA.TABLE_NAME = BB.TABLE_NAME

                     AND AA.INDEX_NAME = BB.CONSTRAINT_NAME) PKCOL

   WHERE       COL.TABLE_NAME = TCOM.TABLE_NAME

           AND COL.TABLE_NAME = CCOM.TABLE_NAME

           AND COL.COLUMN_NAME = CCOM.COLUMN_NAME

           AND COL.TABLE_NAME = 'COUNTRIES'

           AND COL.COLUMN_NAME = PKCOL.COLUMN_NAME(+)

           AND COL.TABLE_NAME = PKCOL.TABLE_NAME(+)

ORDER BY   COL.TABLE_NAME, COL.COLUMN_ID

USER 가 아닌 ALL 로 시작하는 테이블로 정보를 알수 있는 것도 있다.

조회하면 너무 많으므로 OWNER 필드에 사용자 계정을 조건으로 걸자.

1. ALL_CATALOG : 모든 테이블, 뷰정보 조회

2. ALL_OBJECT_TABLES : 모든 오브젝트 테이블 정보

3. ALL_TAB_COMMENTS : 모든 테이블 주석 정보

4. ALL_TYPES : 모든 오브젝트 타입에 대한 정보

5. ALL_USERS : 모든 사용자 정보

 

2012-11-2 추가 자료 : 시스템 정보 조회 테이블

 

추가로 시스템 정보를 조회할수 있는 테이블 목록입니다.

* ALL_OBJECTS : 오브젝트 조회

select * from all_objects where object_name like '오브젝트명';

 

* ALL_SYNONYMS : 시노팀 조회

select * from all_synonyms where synonym_name='시노님명';

 

* ALL_IND_COLUMNS : 테이블 인덱스 정보 조회  

select * from all_ind_columns where table_name='테이블명';

 

* ALL_TAB_COLUMNS : 테이블별 컬럼정보 조회  

select * from all_tab_columns where table_name='테이블명';

 

* ALL_COL_COMMENTS : 테이블 컬럼 comment 조회  

select * from all_col_comments where table_name='테이블명';