[Database] Oracle 시스템 카탈로그 추출하기, 테이블 , 컬럼, 코맨트 정보 확인하기

DA업무를 맡으며, 그간 DBA보고 뽑아달라 했던 테이블 및 컬럼 정보들이 필요하게 됐는데, DBA가 없더라도 제법 추출하기  쉬운 항목들이므로, 직접 뽑아보면서 정리해봤습니다.

1. 테이블 정보

SELECT *
FROM DBA_TABLES; -- 데이터베이스 내의 모든 테이블

SELECT *
FROM ALL_TABLES; -- 유저가 소유한 테이블, 유저가 접근가능한 테이블

SELECT *
FROM USER_TABLES; -- 유저가 소유한 테이블 (현재 유저의 소유목록이기때문에 OWNER 정보가 없다.)

DBA_TABLES 출력예시

---

2. 컬럼 정보

SELECT *
FROM DBA_TAB_COLUMNS; -- 데이터베이스내의 모든 컬럼 목록(숨겨진 컬럼 제외)

SELECT *
FROM ALL_TAB_COLUMNS; -- 유저가 소유한 컬럼, 유저가 접근 가능한 컬럼 목록(숨겨진 컬럼 제외)

SELECT *
FROM USER_TAB_COLUMNS; -- 유저가 소유한 컬럼 목록(숨겨진 컬럼 제외) (현재 유저의 소유목록이기때문에 OWNER 정보가 없다.)

SELECT *
FROM DBA_TAB_COLS; -- 데이터베이스내의 모든 컬럼 목록(숨겨진 컬럼 포함)

SELECT *
FROM ALL_TAB_COLS; --유저가 소유한 컬럼, 유저가 접근 가능한 컬럼 목록(숨겨진 컬럼 포함)

SELECT *
FROM USER_TAB_COLS; --유저가 소유한 컬럼 목록(숨겨진 컬럼 포함) (현재 유저의 소유목록이기때문에 OWNER 정보가 없다.)

DBA_TAB_COLUMNS 출력 예시

DBA_TAB_COLS 출력 예시

---

3. 테이블 및 컬럼 코맨트

SELECT *
FROM DBA_TAB_COMMENTS; -- 데이터베이스 내의 모든 테이블과 코맨트

SELECT *
FROM ALL_TAB_COMMENTS; -- 유저가 소유하거나 접근가능한 모든 테이블과 코맨트

SELECT *
FROM USER_TAB_COMMENTS; -- 유저가 소유한 모든 테이블과 코맨트 (현재 유저의 소유목록이기때문에 OWNER 정보가 없다.)

SELECT *
FROM DBA_COL_COMMENTS; -- 데이터베이스 내의 모든 컬럼과 코맨트

SELECT *
FROM ALL_COL_COMMENTS; -- 유저가 소유하거나 접근가능한 모든 컬럼과 코맨트

SELECT *
FROM USER_COL_COMMENTS; -- 유저가 소유한 모든 테이블과 코맨트 (현재 유저의 소유목록이기때문에 OWNER 정보가 없다.)

DBA_TAB_COMMENTS 출력 예시

DBA_COL_COMMENTS 출력 예시

---

4. 테이블, 컬럼 코맨트 조합하기

1) 특정 오너

SELECT A.OWNER
      ,A.TABLE_NAME
      ,B.COLUMN_NAME
      ,B.COMMENTS
FROM DBA_TABLES A
INNER JOIN DBA_COL_COMMENTS B
ON (A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME)-- 테이블정보와 COLUMN 코맨트를 오너와 테이블이름으로 조인
WHERE A.OWNER = 'SCOTT';

2) 특정 테이블

SELECT A.OWNER
      ,A.TABLE_NAME
      ,B.COLUMN_NAME
      ,B.COMMENTS
FROM DBA_TABLES A
INNER JOIN DBA_COL_COMMENTS B
ON (A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME)-- 테이블정보와 COLUMN 코맨트를 오너와 테이블이름으로 조인
WHERE A.TABLE_NAME = 'EMP';

5. 공통코드 테이블 정보

1) 공통코드 테이블 찾기

SELECT *
FROM ALL_TAB_COMMENTS
WHERE COMMENTS LIKE '%공통코드%';

2) 그룹코드, 공통코드 테이블 조인하기

SELECT A.GROUP_CD -- 그룹코드 (MAJOR_CD, CODE_ID 라고도 불림)
      ,A.GROUP_NM -- 그룹코드명 (MAJOR_NM, CODE_NM 라고도 불림)
      ,B.COMMON_CD -- 공통코드 (MINOR_CD, CD_VAL 라고도 불림)
      ,B.COMMON_NM -- 공통코드명 (MINOR_NM, CD_VAL_NM 라고도 불림)
 FROM GROUP_CD_TABLE_NAME A -- 그룹코드 테이블
INNER JOIN COMMON_CD_TABLE_NAME B -- 공통코드 테이블
ON A.GROUP_CD = B.GROUP_CD; -- GROUP_CD를 키로 조인

---

6. 오브젝트(Objects) 정보 

SELECT *
FROM DBA_OBJECTS; -- 데이터베이스 내의 모든 OBJECTS

SELECT *
FROM ALL_OBJECTS; -- 유저가 소유한 OBJECTS, 다른 유저의 OBJECTS중 접근 권한이 있는 OBJECTS목록

SELECT *
FROM USER_OBJECTS; -- 유저가 소유한 OBJECTS 목록 (현재 유저의 소유목록이기때문에 OWNER 정보가 없다.)

DBA_OBJECT 출력 예시

OBJECT_TYPE 종류에는 아래와 같이 있습니다.

• EDITION
• INDEX PARTITION
• CONSUMER GROUP
• SEQUENCE
• TABLE PARTITION
• SCHEDULE
• QUEUE
• RULE
• JAVA DATA
• PROCEDURE
• OPERATOR
• LOB PARTITION
• DESTINATION
• WINDOW
• SCHEDULER GROUP
• LOB
• PACKAGE
• PACKAGE BODY
• LIBRARY
• PROGRAM
• RULE SET
• CONTEXT
• TYPE BODY
• JAVA RESOURCE
• XML SCHEMA
• TRIGGER
• JOB CLASS
• UNDEFINED
• DIRECTORY
• MATERIALIZED VIEW
• DIMENSION
• TABLE
• INDEX
• SYNONYM
• VIEW
• FUNCTION
• JAVA CLASS
• JAVA SOURCE
• INDEXTYPE
• CLUSTER
• TYPE
• RESOURCE PLAN
• JOB
• EVALUATION CONTEXT

---

7. 제약사항(Constraints) 정보

SELECT *
FROM DBA_CONSTRAINTS; -- 데이터베이스 내의 모든 제약조건 목록

SELECT *
FROM ALL_CONSTRAINTS; -- 유저가 소유하거나 접근가능한 제약조건 목록

SELECT *
FROM USER_CONSTRAINTS; -- 유저가 소유한 제약조건 목록

DBA_CONSTRAINTS 출력 예시

• V: CHECK OPTION ON A VIEW
• U: UNIQUE KEY
• R: REFRENTIAL (FOREIGN KEY)
• P: PRIMARY KEY
• C: CHECK ON A TABLE (NOT NULL)
• O: READ ONLY ON A VIEW
• F: DEFAULT