[DB] Oracle 데이터베이스 메타데이터 조회 가이드

Oracle 데이터베이스 메타데이터 조회 완벽 가이드

오라클 데이터베이스를 관리하다 보면 테이블의 구조, 컬럼 정보, 인덱스, 제약조건 등 다양한 메타데이터를 조회해야 하는 경우가 많습니다. 개발자와 DBA는 데이터베이스 구조를 정확히 파악하여 효율적인 쿼리를 작성하고, 성능을 최적화하며, 데이터 무결성을 보장해야 합니다.

오라클은 데이터 딕셔너리(Data Dictionary)라는 시스템 뷰를 제공하여 데이터베이스의 모든 메타데이터를 쉽게 조회할 수 있습니다. 이러한 뷰들을 활용하면 SQL 쿼리만으로 필요한 정보를 빠르게 확인할 수 있습니다.

이 글에서는 오라클 데이터베이스에서 테이블과 관련된 주요 메타데이터를 조회하는 방법을 초보자도 쉽게 따라할 수 있도록 상세히 설명해드리겠습니다. 실무에서 바로 활용할 수 있는 다양한 SQL 쿼리 예제와 함께 설명하니 끝까지 읽어보시기 바랍니다.

목차

1. 오라클 데이터 딕셔너리와 테이블 조회

2. 테이블 컬럼 정보와 스키마 조회

3. 인덱스와 제약조건 조회 방법

4. 기본키와 외래키 조회 방법

5. 자주 묻는 질문 (FAQ)

---

#1. 오라클 데이터 딕셔너리와 테이블 조회

오라클 데이터베이스는 메타데이터를 조회하기 위해 세 가지 종류의 뷰를 제공합니다. 각 뷰의 특징을 이해하면 상황에 맞는 적절한 뷰를 선택할 수 있습니다.

1) 데이터 딕셔너리 뷰의 종류

뷰 종류	설명	권한
USER_	현재 로그인한 사용자가 소유한 객체만 조회	기본 권한
ALL_	현재 사용자가 소유하거나 접근 권한이 있는 모든 객체 조회	기본 권한
DBA_	데이터베이스의 모든 객체 조회 (전체 시스템)	DBA 권한 필요

-- USER_ 뷰: 내가 소유한 테이블만 조회
SELECT * FROM USER_TABLES;

-- ALL_ 뷰: 접근 가능한 모든 테이블 조회
SELECT * FROM ALL_TABLES;

-- DBA_ 뷰: 전체 데이터베이스의 모든 테이블 (DBA 권한 필요)
SELECT * FROM DBA_TABLES;

. . . . .

2) 주요 데이터 딕셔너리 뷰

① USER_TABLES - 테이블 정보

② USER_TAB_COLUMNS - 컬럼 정보

③ USER_INDEXES - 인덱스 정보

④ USER_CONSTRAINTS - 제약조건 정보

⑤ USER_CONS_COLUMNS - 제약조건 컬럼 정보

. . . . .

3) 테이블명 조회하기

-- 기본 테이블 목록 조회
SELECT TABLE_NAME
FROM USER_TABLES
ORDER BY TABLE_NAME;

-- 테이블명, 생성일, 행 개수 등 포함
SELECT
    TABLE_NAME AS "테이블명",
    NUM_ROWS AS "행개수",
    TO_CHAR(LAST_ANALYZED, 'YYYY-MM-DD') AS "마지막분석일자"
FROM USER_TABLES
ORDER BY TABLE_NAME;

-- EMP로 시작하는 테이블만 조회
SELECT TABLE_NAME
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'EMP%'
ORDER BY TABLE_NAME;

---

#2. 테이블 컬럼 정보와 스키마 조회

테이블의 구조를 파악하기 위해 컬럼 정보를 조회하는 것은 매우 중요합니다.

1) 특정 테이블의 컬럼 조회

-- EMPLOYEES 테이블의 컬럼 정보 조회
SELECT
    COLUMN_NAME AS "컬럼명",
    DATA_TYPE AS "데이터타입",
    DATA_LENGTH AS "길이",
    NULLABLE AS "NULL허용"
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES'
ORDER BY COLUMN_ID;

. . . . .

2) 컬럼 주석 포함 조회

-- 컬럼 설명(주석) 포함
SELECT
    C.COLUMN_NAME AS "컬럼명",
    C.DATA_TYPE AS "데이터타입",
    M.COMMENTS AS "설명"
FROM USER_TAB_COLUMNS C
LEFT JOIN USER_COL_COMMENTS M
    ON C.TABLE_NAME = M.TABLE_NAME
    AND C.COLUMN_NAME = M.COLUMN_NAME
WHERE C.TABLE_NAME = 'EMPLOYEES'
ORDER BY C.COLUMN_ID;

. . . . .

3) 현재 사용자 확인

-- 현재 로그인한 사용자 확인
SELECT USER FROM DUAL;

-- 현재 세션 정보 확인
SELECT
    SYS_CONTEXT('USERENV', 'SESSION_USER') AS "세션사용자",
    SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS "현재스키마"
FROM DUAL;

---

#3. 인덱스와 제약조건 조회 방법

1) 테이블 인덱스 조회하기

-- EMPLOYEES 테이블의 인덱스 조회
SELECT
    INDEX_NAME AS "인덱스명",
    INDEX_TYPE AS "타입",
    UNIQUENESS AS "유일성"
FROM USER_INDEXES
WHERE TABLE_NAME = 'EMPLOYEES'
ORDER BY INDEX_NAME;

-- 인덱스별 컬럼 정보
SELECT
    INDEX_NAME AS "인덱스명",
    COLUMN_NAME AS "컬럼명",
    COLUMN_POSITION AS "순서"
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES'
ORDER BY INDEX_NAME, COLUMN_POSITION;

. . . . .

2) 테이블 제약조건 조회하기

타입	의미	설명
P	Primary Key	기본키
R	Foreign Key	외래키
U	Unique	유일키
C	Check	체크 제약조건

-- 제약조건 조회
SELECT
    CONSTRAINT_NAME AS "제약조건명",
    CASE CONSTRAINT_TYPE
        WHEN 'P' THEN '기본키'
        WHEN 'R' THEN '외래키'
        WHEN 'U' THEN '유일키'
        WHEN 'C' THEN '체크'
    END AS "타입"
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES'
ORDER BY CONSTRAINT_TYPE;

---

#4. 기본키와 외래키 조회 방법

1) 기본키 조회

-- 기본키 조회
SELECT
    C.CONSTRAINT_NAME AS "기본키명",
    CC.COLUMN_NAME AS "컬럼명"
FROM USER_CONSTRAINTS C
JOIN USER_CONS_COLUMNS CC
    ON C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
WHERE C.TABLE_NAME = 'EMPLOYEES'
  AND C.CONSTRAINT_TYPE = 'P'
ORDER BY CC.POSITION;

. . . . .

2) 외래키 참조 관계 조회

-- 외래키와 참조 테이블 정보
SELECT
    C.CONSTRAINT_NAME AS "외래키명",
    C.TABLE_NAME AS "테이블명",
    CC.COLUMN_NAME AS "컬럼명",
    R.TABLE_NAME AS "참조테이블",
    RC.COLUMN_NAME AS "참조컬럼"
FROM USER_CONSTRAINTS C
JOIN USER_CONS_COLUMNS CC
    ON C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
JOIN USER_CONSTRAINTS R
    ON C.R_CONSTRAINT_NAME = R.CONSTRAINT_NAME
JOIN USER_CONS_COLUMNS RC
    ON R.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'R'
ORDER BY C.CONSTRAINT_NAME;

---

마무리

오라클 데이터베이스의 메타데이터 조회 방법에 대해 알아보았습니다. 핵심 정리:

① 데이터 딕셔너리 뷰 활용 - USER_, ALL_, DBA_ 뷰를 상황에 맞게 사용하세요.

② 주요 조회 포인트 - 테이블 구조, 인덱스, 제약조건, 기본키/외래키 관계 파악이 중요합니다.

③ 실무 활용 팁 - 자주 사용하는 쿼리는 북마크하거나 스크립트 파일로 저장하세요.

출처: Oracle Database 공식 문서(https://docs.oracle.com/en/database/)를 참고하여 작성되었습니다.

---

#5. 자주 묻는 질문 (FAQ)

1) Q: USER_, ALL_, DBA_ 뷰 중 어떤 것을 사용해야 하나요?

A: 대부분의 경우 USER_ 뷰를 사용하면 됩니다. USER_ 뷰는 가장 빠르고 간단합니다. 다른 스키마 접근은 ALL_ 뷰, DBA 작업은 DBA_ 뷰를 사용하세요.

. . . . .

2) Q: DESC 명령어와 USER_TAB_COLUMNS의 차이는?

A: DESC는 빠른 확인용으로 기본 정보만 보여주고, USER_TAB_COLUMNS는 상세 정보와 가공이 가능합니다. 빠른 확인은 DESC, 상세 분석은 USER_TAB_COLUMNS를 사용하세요.

. . . . .

3) Q: 기본키가 없는 테이블을 사용해도 되나요?

A: 강력히 권장하지 않습니다. 기본키는 데이터 무결성을 보장하는 가장 기본적인 제약조건입니다. 임시 테이블을 제외하고는 반드시 기본키를 정의하세요.

긴 글 읽어주셔서 감사합니다.

끝.