오라클 DBMS_METADATA로 메타데이터 추출하기

 

오라클 데이터베이스에서 DBMS_METADATA 패키지는 데이터베이스 객체의 메타데이터를 추출하는 데 사용됩니다. 테이블, 인덱스, 뷰, 트리거 등 다양한 객체의 DDL(Data Definition Language) 스크립트를 생성할 수 있어 데이터베이스 관리, 백업, 마이그레이션 등에 유용합니다.

이 글에서는 DBMS_METADATA 패키지의 개념, 사용법, 그리고 주요 객체의 메타데이터를 추출하는 실무 예제를 소개합니다.

---

1. DBMS_METADATA란?

DBMS_METADATA는 오라클이 제공하는 내장 패키지로, 데이터베이스 객체의 메타데이터를 SQL 형식으로 반환합니다.
이를 통해 객체 생성 스크립트를 쉽게 추출할 수 있으며, 스키마 마이그레이션이나 데이터베이스 비교 작업 시 활용됩니다.

주요 기능:

• 테이블, 인덱스, 뷰, 트리거 등 객체의 DDL 생성.
• 특정 스키마나 조건에 맞는 객체 추출.
• 기본 옵션 외에 사용자 정의 형식으로 결과 반환 가능.

---

2. DBMS_METADATA 사용 방법

기본 문법

SELECT DBMS_METADATA.GET_DDL('객체_타입', '객체_이름', '스키마_이름') 
FROM DUAL;

• 객체_타입: 추출할 객체의 종류 (예: 'TABLE', 'INDEX', 'VIEW').
• 객체_이름: 추출할 객체의 이름.
• 스키마_이름: 객체가 속한 스키마 이름.

예시: 테이블 메타데이터 추출

SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') 
FROM DUAL;

결과:

CREATE TABLE "HR"."EMPLOYEES" 
   ( "EMPLOYEE_ID" NUMBER(6,0), 
     "FIRST_NAME" VARCHAR2(20), 
     "LAST_NAME" VARCHAR2(25) NOT NULL ENABLE, 
     ...
   ) ;

---

3. 주요 객체의 메타데이터 추출 예제

예제 1: 특정 테이블의 DDL 추출

아래는 HR 스키마에 있는 EMPLOYEES 테이블의 DDL을 추출하는 예제입니다.

SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') 
FROM DUAL;

예제 2: 전체 스키마 내 테이블 DDL 추출

전체 테이블의 DDL을 추출하려면 ALL_TABLES 또는 USER_TABLES를 활용합니다.

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) 
FROM USER_TABLES;

이 쿼리는 현재 사용자의 스키마에 있는 모든 테이블의 DDL을 반환합니다.

예제 3: 인덱스 DDL 추출

특정 인덱스의 DDL을 추출하려면 다음과 같이 작성합니다.

SELECT DBMS_METADATA.GET_DDL('INDEX', 'EMP_IDX', 'HR') 
FROM DUAL;

예제 4: 뷰 DDL 추출

뷰 생성 스크립트를 추출하려면 객체 유형을 'VIEW'로 설정합니다.

SELECT DBMS_METADATA.GET_DDL('VIEW', 'EMPLOYEE_VIEW', 'HR') 
FROM DUAL;

예제 5: 트리거 DDL 추출

트리거의 DDL을 추출하려면 'TRIGGER' 객체 유형을 사용합니다.

SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'EMPLOYEE_TRG', 'HR') 
FROM DUAL;

---

4. DBMS_METADATA의 옵션 설정

XML 형식으로 반환

DBMS_METADATA는 기본적으로 DDL을 텍스트 형식으로 반환하지만, XML 형식으로도 추출할 수 있습니다.

SELECT DBMS_METADATA.GET_XML('TABLE', 'EMPLOYEES', 'HR') 
FROM DUAL;

이 형식은 데이터 변환 또는 데이터베이스 간 호환성을 위해 유용합니다.

---

필터 설정

특정 속성만 포함하거나 제외하려면 필터를 설정할 수 있습니다.

예: 저장된 주석을 포함하지 않도록 설정

BEGIN
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false);
END;

---

5. 실무 활용 사례

사례 1: 데이터베이스 마이그레이션

• 목적: 스키마 객체를 새로운 환경으로 이전.
• 방법: DBMS_METADATA로 모든 객체의 DDL을 추출한 후 대상 데이터베이스에 적용.

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) 
FROM USER_TABLES;

사례 2: 객체 복원

• 목적: 특정 객체 삭제 후 복원 필요.
• 방법: 사전에 DBMS_METADATA로 DDL 백업을 추출해 둠.

사례 3: 버전 관리

• 목적: 데이터베이스 구조 변경을 추적.
• 방법: DBMS_METADATA로 생성된 DDL을 파일로 저장하여 버전 관리 도구(Git 등)와 통합.

---

6. 주의사항 및 팁

1. 권한 문제: 다른 사용자의 스키마에서 메타데이터를 추출하려면 SELECT_CATALOG_ROLE 또는 SELECT ANY DICTIONARY 권한이 필요합니다.
2. 복잡한 객체: 복잡한 뷰나 트리거의 경우 메타데이터를 추출할 때 추가 설정이 필요할 수 있습니다.
3. 대용량 데이터베이스: 스키마 전체의 DDL을 추출하면 대량의 결과가 반환될 수 있으므로, 필요한 객체만 선택적으로 추출하는 것이 좋습니다.

---

마무리

DBMS_METADATA는 오라클 데이터베이스 관리에서 강력한 도구로, 데이터베이스 객체의 DDL 추출 및 관리 작업을 간소화합니다. 실무에서 이 패키지를 활용하면 데이터베이스 마이그레이션, 백업, 구조 변경 관리 등의 작업을 효과적으로 수행할 수 있습니다. 이번 포스팅에서 소개한 개념과 예제를 활용해 보시기 바랍니다!