시스템 카탈로그로 읽는 DBMS 메타데이터와 객체 관리

DBMS가 참조하는 메타데이터 저장소

시스템 카탈로그는 DBMS가 데이터베이스 객체와 사용자 접근을 관리하기 위해 사용하는 메타데이터 저장소다. 데이터베이스 자체에 관한 정보를 담는다는 점에서 ‘데이터베이스에 관한 데이터베이스(Database about database)’로 볼 수 있으며, 데이터 사전(Data Dictionary)이라는 이름으로도 불린다.

DDL로 생성한 테이블, 뷰, 인덱스, 트리거, 저장 프로시저 같은 객체 정의는 카탈로그에 자동으로 기록된다. DBMS는 이 정보를 바탕으로 객체 구조를 파악하고, 권한을 확인하며, 쿼리 실행 계획을 세운다.

객체 정의부터 통계까지 카탈로그가 맡는 일

카탈로그에는 데이터베이스 객체의 정의뿐 아니라 사용자 계정, 역할, 권한 정보도 보관된다. 기본키, 외래키, CHECK 제약조건 같은 무결성 제약조건 역시 관리 대상이다.

쿼리 최적화기는 실행 계획을 수립할 때 카탈로그의 통계 정보를 참조한다. 객체 변경 이력은 백업과 복구 작업에도 쓰인다. 따라서 카탈로그는 단순한 조회용 메타데이터가 아니라 DBMS의 관리 기능을 뒷받침하는 기준 정보다.

카탈로그에 담기는 정보

시스템 카탈로그는 대체로 테이블 또는 뷰 형태로 제공된다. 테이블과 컬럼 정의, 인덱스와 제약조건, 사용자와 권한, 저장 프로시저와 트리거, 통계 정보가 대표적인 구성이다.

DBMS별 카탈로그 접근 방식

Oracle 데이터베이스

Oracle은 시스템 카탈로그를 Data Dictionary라고 부른다. 실제 테이블(BASE TABLE)과 사용하기 쉬운 뷰(VIEW)로 구성되며, 주요 뷰는 DBA_, ALL_, USER_ 접두어로 구분한다.

• DBA_: 모든 객체 정보(DBA 권한 필요)
• ALL_: 접근 가능한 객체 정보
• USER_: 현재 사용자가 소유한 객체 정보

-- Oracle에서 테이블 정보 조회 예시
SELECT * FROM DBA_TABLES WHERE OWNER = 'SCOTT';

-- 특정 테이블의 컬럼 정보 조회
SELECT * FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'EMP';

Microsoft SQL Server

SQL Server는 System Catalog Views를 제공하며, sys 스키마에 정의된 뷰를 통해 접근한다. ANSI 표준 호환 뷰는 INFORMATION_SCHEMA에서 조회할 수 있다.

-- SQL Server에서 테이블 정보 조회 예시
SELECT * FROM sys.tables;

-- 특정 테이블의 컬럼 정보 조회
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Employees');

-- ANSI 표준 뷰 사용 예시
SELECT * FROM INFORMATION_SCHEMA.TABLES;

MySQL

MySQL은 INFORMATION_SCHEMA 데이터베이스에 시스템 카탈로그를 저장한다. 이 인터페이스는 ANSI SQL 표준을 준수하는 뷰를 제공한다.

-- MySQL에서 테이블 정보 조회 예시
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'employees';

-- 특정 테이블의 컬럼 정보 조회
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'employees';

PostgreSQL

PostgreSQL의 시스템 카탈로그는 pg_catalog 스키마에 저장된다. 카탈로그 조회를 위한 다양한 함수도 함께 제공한다.

-- PostgreSQL에서 테이블 정보 조회 예시
SELECT * FROM pg_catalog.pg_tables WHERE schemaname = 'public';

-- 특정 테이블의 컬럼 정보 조회
SELECT * FROM pg_catalog.pg_attribute WHERE attrelid = 'employees'::regclass;

설계 검증과 운영 분석에 쓰는 방법

카탈로그 조회는 테이블, 컬럼, 제약조건 정보를 비교해 설계의 일관성을 확인하는 데 쓸 수 있다. 누락된 인덱스나 제약조건을 식별해 설계를 보완하는 작업도 여기에 포함된다.

-- Oracle에서 외래키가 없는 테이블 찾기
SELECT table_name
FROM dba_tables
WHERE owner = 'SCHEMA_NAME'
  AND table_name NOT IN (
    SELECT table_name
    FROM dba_constraints
    WHERE owner = 'SCHEMA_NAME'
      AND constraint_type = 'R'
  );

인덱스와 통계 정보는 성능 병목을 찾는 단서가 된다. 사용되지 않는 인덱스를 식별하면 불필요한 오버헤드를 제거할 수 있다.

-- SQL Server에서 사용되지 않는 인덱스 찾기
SELECT
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius ON
    i.object_id = ius.object_id AND
    i.index_id = ius.index_id
WHERE
    OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND ius.user_seeks = 0
    AND ius.user_scans = 0
    AND ius.user_lookups = 0
ORDER BY ius.user_updates DESC;

권한과 역할 정보는 보안 감사의 대상이 된다. 과도하게 부여된 권한을 찾고, 민감한 데이터에 대한 접근 권한을 검증할 때 카탈로그를 확인한다.

-- Oracle에서 DBA 권한을 가진 사용자 조회
SELECT grantee
FROM dba_role_privs
WHERE granted_role = 'DBA'
  AND grantee NOT IN ('SYS', 'SYSTEM');

객체 정의를 추출하면 데이터베이스 문서화도 자동화할 수 있다. 테이블 구조를 정리하거나 ERD(Entity-Relationship Diagram)를 생성하는 과정에서 활용한다.

-- PostgreSQL에서 테이블 구조 문서화를 위한 정보 추출
SELECT
    c.table_name,
    c.column_name,
    c.data_type,
    c.character_maximum_length,
    c.is_nullable,
    pgd.description
FROM
    information_schema.columns c
LEFT JOIN
    pg_catalog.pg_description pgd ON
    pgd.objoid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = c.table_name)
    AND pgd.objsubid = c.ordinal_position
WHERE
    c.table_schema = 'public'
ORDER BY
    c.table_name, c.ordinal_position;

카탈로그 조회를 운영에 반영할 때

시스템 카탈로그는 DBMS가 자동으로 관리하므로 직접 수정해서는 안 된다. 변경하면 데이터베이스 무결성이 훼손될 수 있다.

대규모 시스템에서는 카탈로그 조회 자체가 성능에 영향을 줄 수 있으므로 필요한 범위로 필터링해야 한다. 접근 권한도 필요한 사용자에게만 제한적으로 부여한다. 또한 카탈로그 구조는 DBMS 버전에 따라 달라질 수 있으므로 버전 변경 시 확인이 필요하다.

변경 전 종속 객체를 확인하는 흐름

테이블처럼 변경 영향이 넓은 객체는 카탈로그를 기반으로 종속성을 먼저 확인할 수 있다. 영향을 받는 뷰, 트리거, 제약조건을 식별한 뒤 객체별 영향을 분석하고 변경 계획을 수립하는 방식이다.

⤢✕

Oracle에서 종속 객체 찾기

-- 특정 테이블 변경 시 영향 받는 뷰, 트리거, 제약조건 분석
WITH dependent_objects AS (
    -- 영향 받는 뷰 확인
    SELECT 'VIEW' as object_type, view_name as object_name
    FROM dba_views
    WHERE UPPER(text) LIKE '%TARGET_TABLE%'

    UNION ALL

    -- 영향 받는 트리거 확인
    SELECT 'TRIGGER' as object_type, trigger_name as object_name
    FROM dba_triggers
    WHERE table_name = 'TARGET_TABLE'

    UNION ALL

    -- 영향 받는 외래키 확인
    SELECT 'FOREIGN KEY' as object_type, constraint_name as object_name
    FROM dba_constraints
    WHERE r_constraint_name IN (
        SELECT constraint_name
        FROM dba_constraints
        WHERE table_name = 'TARGET_TABLE'
        AND constraint_type IN ('P', 'U')
    )
)
SELECT * FROM dependent_objects ORDER BY object_type, object_name;

시스템 카탈로그의 구현 방식은 DBMS마다 다르지만, 객체 정의와 접근 제어, 성능 정보라는 기본 역할은 유사하다. 데이터베이스 관리자, 개발자, 데이터 아키텍트에게 설계·최적화·보안·문서화 전반의 판단 근거를 제공한다.