사전 테이블 및 뷰

사전 테이블(Dictionary Tables)과 뷰(Views) 소개

Oracle 데이터베이스의 사전(Dictionary)은 데이터베이스 자체에 대한 메타데이터를 저장하는 핵심적인 부분입니다. 이러한 사전은 테이블과 뷰 형태로 제공되며, 데이터베이스 관리자(DBA)와 개발자가 데이터베이스의 구조, 객체, 설정 등을 이해하고 관리하는 데 필수적인 정보를 제공합니다.

사전 테이블과 뷰의 중요성

  • 데이터베이스 구조 이해: 데이터베이스 내의 모든 테이블, 인덱스, 사용자, 권한 등의 정보를 제공하여 전체적인 구조를 파악하는 데 도움을 줍니다.
  • 객체 관리: 테이블, 뷰, 프로시저 등 다양한 데이터베이스 객체의 속성, 상태, 의존성 등을 확인하고 관리할 수 있습니다.
  • 성능 튜닝: 성능 관련 통계 정보를 제공하여 병목 현상을 진단하고 성능 개선을 위한 튜닝 작업을 수행할 수 있도록 지원합니다.
  • 보안 관리: 사용자 권한, 감사 설정 등 보안 관련 정보를 제공하여 데이터베이스 보안 정책을 수립하고 관리하는 데 활용됩니다.

주요 사전 테이블 및 뷰

DBA_TABLES

DBA_TABLES는 데이터베이스 내의 모든 테이블에 대한 정보를 제공합니다. 테이블 이름, 소유자, 테이블스페이스, 로우 수 등 기본적인 테이블 속성을 확인할 수 있습니다.


SELECT owner, table_name, tablespace_name, num_rows
FROM dba_tables
WHERE owner = 'HR';

실행 결과 예시:

OWNER | TABLE_NAME | TABLESPACE_NAME | NUM_ROWS
------|------------|-----------------|----------
HR    | EMPLOYEES  | USERS           | 107
HR    | DEPARTMENTS| USERS           | 27
...

DBA_INDEXES

DBA_INDEXES는 데이터베이스 내의 모든 인덱스에 대한 정보를 제공합니다. 인덱스 이름, 테이블 이름, 인덱스 유형 등을 통해 인덱스 상태를 모니터링하고 성능 문제를 진단할 수 있습니다.


SELECT index_name, table_name, index_type, uniqueness
FROM dba_indexes
WHERE table_owner = 'HR';

실행 결과 예시:

INDEX_NAME | TABLE_NAME | INDEX_TYPE | UNIQUENESS
-----------|------------|------------|------------
EMP_EMAIL_UK| EMPLOYEES  | NORMAL     | UNIQUE
DEPT_ID_PK  | DEPARTMENTS| NORMAL     | UNIQUE
...

DBA_USERS

DBA_USERS는 데이터베이스에 등록된 모든 사용자에 대한 정보를 제공합니다. 사용자 이름, 계정 상태, 생성일 등을 확인할 수 있습니다.


SELECT username, account_status, created
FROM dba_users
WHERE username LIKE 'HR%';

실행 결과 예시:

USERNAME | ACCOUNT_STATUS | CREATED
----------|----------------|----------------
HR        | OPEN           | 20-NOV-23
...

DBA_ROLES

DBA_ROLES는 데이터베이스에 정의된 모든 롤에 대한 정보를 제공합니다. 롤 이름, 롤 ID 등을 통해 데이터베이스 권한 설정을 관리할 수 있습니다.


SELECT role, role_id
FROM dba_roles
WHERE role LIKE 'CONNECT%';

실행 결과 예시:

ROLE     | ROLE_ID
---------|----------
CONNECT  | 47
...

DBA_SYS_PRIVS

DBA_SYS_PRIVS는 사용자 또는 롤에 부여된 시스템 권한 정보를 제공합니다. 이를 통해 어떤 사용자 또는 롤이 어떤 시스템 권한을 가지고 있는지 확인할 수 있습니다.


SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'HR';

실행 결과 예시:

GRANTEE | PRIVILEGE    | ADMIN_OPTION
---------|--------------|--------------
HR      | CREATE TABLE | N
...

DBA_TAB_PRIVS

DBA_TAB_PRIVS는 사용자 또는 롤에 부여된 객체 권한 정보를 제공합니다. 특정 테이블 또는 뷰에 대한 사용자 권한을 확인할 수 있습니다.


SELECT grantee, owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = 'PUBLIC'
  AND table_name = 'EMPLOYEES';

실행 결과 예시:

GRANTEE| OWNER| TABLE_NAME| PRIVILEGE
-------|------|-----------|----------
PUBLIC | HR| EMPLOYEES| SELECT

V$SQLAREA

V$SQLAREA는 공유 SQL 영역에 캐시된 SQL 구문에 대한 성능 정보를 제공합니다. 실행 횟수, CPU 시간, 디스크 읽기 횟수 등을 통해 어떤 SQL 구문이 많은 자원을 소모하는지 확인할 수 있습니다.


SELECT sql_text, executions, cpu_time, disk_reads
FROM v$sqlarea
WHERE executions > 100
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

실행 결과 예시:

SQL_TEXT                               | EXECUTIONS| CPU_TIME| DISK_READS
----------------------------------------|-----------|---------|----------
SELECT * FROM employees WHERE ...      | 500       | 0.5     | 1000
UPDATE departments SET ...              | 200       | 0.2     | 500
...

사전 테이블 및 뷰 활용 전략

  • 정기적인 모니터링: 중요한 테이블 및 뷰의 상태를 정기적으로 모니터링하여 성능 저하 또는 보안 문제 발생 가능성을 사전에 감지합니다.
  • 자동화된 스크립트 활용: 스크립트를 작성하여 데이터베이스 상태를 자동적으로 점검하고 리포트를 생성하도록 합니다.
  • 커스터마이징: 필요에 따라 사용자 정의 뷰를 생성하여 특정 정보를 쉽게 확인할 수 있도록 합니다.

결론

Oracle 데이터베이스의 사전 테이블과 뷰는 데이터베이스 관리 및 개발에 있어서 필수적인 도구입니다. 이러한 사전 정보를 효과적으로 활용하면 데이터베이스의 효율적인 관리, 성능 개선, 보안 강화에 기여할 수 있습니다.

위로 스크롤