개발환경 : 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='테이블명';
