오라클 데이터 딕셔너리 뷰 이해하기

오라클 데이터베이스를 관리하거나 쿼리를 최적화하려면 데이터 딕셔너리(Data Dictionary)를 이해하는 것이 중요합니다. 데이터 딕셔너리는 데이터베이스의 메타데이터(데이터에 대한 데이터)를 저장하는 특별한 시스템 테이블과 뷰의 집합입니다. 이러한 뷰를 통해 데이터베이스 객체의 상태, 구조, 권한 및 성능 정보를 확인할 수 있습니다.

이번 글에서는 오라클 데이터 딕셔너리 뷰의 개념과 주요 분류, 자주 사용되는 뷰 및 실무 활용 사례를 살펴보겠습니다.

---

1. 데이터 딕셔너리란?

데이터 딕셔너리는 데이터베이스 자체와 관련된 정보를 저장하는 시스템 메타데이터입니다.
이는 데이터베이스가 생성되거나 업데이트될 때 자동으로 관리되며, 데이터베이스 관리자(DBA)와 사용자 모두에게 중요한 정보를 제공합니다.

데이터 딕셔너리는 다음과 같은 정보를 포함합니다:

• 테이블, 뷰, 인덱스, 트리거 등 데이터베이스 객체 정보
• 사용자 및 권한 정보
• 데이터베이스의 상태와 설정
• 수행된 작업 기록 및 통계 정보

---

2. 데이터 딕셔너리 뷰의 주요 분류

오라클은 데이터 딕셔너리 뷰를 접근 범위와 사용자 권한에 따라 크게 세 가지로 분류합니다.

뷰 종류 설명 접근 범위

USER 뷰	현재 사용자와 관련된 객체 및 정보를 표시.	현재 사용자 소유 객체만
ALL 뷰	현재 사용자가 접근 권한을 가진 모든 객체 정보를 표시.	접근 권한이 있는 모든 객체
DBA 뷰	데이터베이스 내 모든 객체 정보를 표시. 데이터베이스 관리자(DBA)만 접근 가능.	데이터베이스 전체

2.1. 주요 차이점

• USER 뷰: 다른 사용자 소유의 객체에는 접근할 수 없습니다.
• ALL 뷰: 다른 사용자의 객체라도 권한이 있으면 정보를 확인할 수 있습니다.
• DBA 뷰: 시스템 전체를 관리할 수 있는 정보를 제공합니다. DBA 권한이 필요합니다.

---

3. 주요 데이터 딕셔너리 뷰

다음은 오라클에서 자주 사용되는 데이터 딕셔너리 뷰와 그 용도를 정리한 표입니다.

뷰 이름 설명 예시

USER_TABLES	현재 사용자가 소유한 테이블 목록	테이블 이름, 저장 공간 정보
USER_VIEWS	현재 사용자가 소유한 뷰(View) 목록	뷰 정의(SQL 쿼리)
USER_INDEXES	현재 사용자가 소유한 인덱스 목록	인덱스 이름, 유형
USER_TAB_COLUMNS	현재 사용자가 소유한 테이블 또는 뷰의 컬럼(열) 정보	컬럼 이름, 데이터 유형, 길이
USER_CONSTRAINTS	현재 사용자가 소유한 테이블의 제약 조건(Primary Key, Foreign Key 등) 정보	제약 조건 이름, 유형
USER_OBJECTS	현재 사용자가 소유한 모든 객체 정보	테이블, 뷰, 인덱스 등
ALL_USERS	데이터베이스의 모든 사용자 목록	사용자 이름, 생성 날짜
DBA_TABLES	데이터베이스의 모든 테이블 정보 (DBA만 접근 가능)	테이블 크기, 파티션 정보
V$SESSION	데이터베이스에 연결된 현재 세션 정보	세션 ID, 사용자 ID, 클라이언트 정보
V$SQL	실행된 SQL 문에 대한 정보	SQL 텍스트, 실행 횟수, 사용된 자원

---

4. 데이터 딕셔너리 뷰 사용법

4.1. 테이블 정보 조회

현재 사용자가 소유한 모든 테이블 목록을 조회합니다.

SELECT table_name
FROM user_tables;

4.2. 특정 테이블의 컬럼 정보 확인

employees 테이블의 모든 컬럼 정보를 확인합니다.

SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';

4.3. 인덱스 정보 확인

현재 사용자 소유의 인덱스를 확인합니다.

SELECT index_name, table_name, uniqueness
FROM user_indexes;

4.4. 제약 조건 확인

특정 테이블에 설정된 제약 조건(Primary Key, Foreign Key 등)을 확인합니다.

SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

4.5. 연결된 세션 정보 확인

현재 데이터베이스에 연결된 모든 세션 정보를 확인합니다.

SELECT sid, serial#, username, status, machine
FROM v$session;

---

5. 실무 활용 사례

사례 1: 테이블 및 컬럼 정보 문서화

• 요구 사항: 테이블과 컬럼 정보를 문서화하여 데이터 모델링에 활용.
• 해결 방법: USER_TAB_COLUMNS 뷰를 활용하여 정보를 추출합니다.

SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns;

사례 2: 성능 분석

• 요구 사항: 성능이 저하된 쿼리를 분석.
• 해결 방법: V$SQL 뷰에서 자주 실행되거나 리소스를 많이 사용하는 쿼리를 추적합니다.

SELECT sql_text, executions, buffer_gets
FROM v$sql
WHERE executions > 10
ORDER BY buffer_gets DESC;

사례 3: 데이터베이스 객체 점검

• 요구 사항: 특정 사용자 계정의 모든 객체를 점검.
• 해결 방법: USER_OBJECTS 뷰를 사용하여 객체 목록을 확인합니다.

SELECT object_name, object_type, status
FROM user_objects;

사례 4: 권한 문제 해결

• 요구 사항: 다른 사용자가 소유한 테이블 접근 권한을 확인.
• 해결 방법: ALL_TAB_PRIVS 뷰에서 권한 정보를 조회합니다.

SELECT grantee, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'EMPLOYEES';

---

6. 데이터 딕셔너리 뷰 사용 시 주의사항

1. 권한 관리:
   • USER 뷰는 자신의 객체만 볼 수 있지만, DBA 뷰는 모든 데이터를 표시하므로 보안에 주의해야 합니다.
2. 실시간 성능 영향:
   • V$ 뷰와 같은 동적 성능 뷰는 실시간 데이터를 제공하므로 쿼리 실행 시 성능 영향을 미칠 수 있습니다.
3. 대규모 데이터:
   • 데이터 딕셔너리 뷰는 대규모 시스템에서는 많은 데이터를 반환할 수 있으므로 적절한 필터링이 필요합니다.

---

7. 마무리

오라클 데이터 딕셔너리 뷰는 데이터베이스의 상태를 파악하고 문제를 해결하는 데 필수적인 도구입니다.
USER, ALL, DBA 뷰를 적절히 활용하면 데이터베이스 객체 관리부터 성능 최적화까지 폭넓은 작업을 수행할 수 있습니다.
정기적으로 데이터 딕셔너리 뷰를 점검하고 실무에서 적극적으로 활용하여 데이터베이스 관리의 전문성을 높이세요!