Oracle 9i 新利器：DBMS_METADATA 輕鬆提取完整 DDL 結構定義

痛點背景

DBA 常需提取表、索引、預存程序、觸發器等結構定義（DDL）以遷移系統或預建物件。Oracle 9i 前：

• EXP ROWS=NO 匯出：輸出格式差、含引用字串、難重用
• 手寫字典腳本：複雜、難支援 IOT、嵌套表等進階物件

DBMS_METADATA 革命性突破

Oracle 9i 引入 DBMS_METADATA 套件，可直接從資料字典提取可立即執行的標準 DDL，支援單一物件至完整 Schema。

核心函數：GET_DDL

DBMS_METADATA.GET_DDL(
  object_type IN VARCHAR2,  -- 物件類型：TABLE, INDEX, PROCEDURE...
  name        IN VARCHAR2,  -- 物件名稱
  schema      IN VARCHAR2 DEFAULT NULL,  -- Schema 名稱
  version     IN VARCHAR2 DEFAULT 'COMPATIBLE',
  model       IN VARCHAR2 DEFAULT 'ORACLE',
  transform   IN VARCHAR2 DEFAULT 'DDL'
) RETURN CLOB;

實戰範例：提取 EMP 表與索引完整定義

-- 單一物件
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX', 'PK_EMP', 'SCOTT') FROM DUAL;

-- 完整 Schema 級別
SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name, u.owner)
FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX', u.index_name, u.owner)
FROM USER_INDEXES u;

輸出特色

• 包含完整儲存參數：PCTFREE、PCTUSED、FREELISTS、FREELIST GROUPS
• 含 Tablespace 指定、Buffer Pool 指令
• Index 定義完整保留
• 僅缺每個陳述式結尾的分號（;），易補充

遷移實戰流程優化

1. 目標庫建立 Schema 結構：使用 DBMS_METADATA 提取 DDL → 執行建立物件
2. 資料匯入：使用 IMP IGNORE=Y 導入資料（忽略已存在物件錯誤）
3. 驗證比對：對比來源/目標物件數量、大小、狀態

進階應用技巧

轉換參數控制輸出細節

-- 移除儲存條款（跨平台遷移時有用）
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);

-- 移除 Tablespace 指定
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);

-- 包含授權語句
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'GRANTS', TRUE);

依賴關係處理

套件自動處理物件依賴順序（如先建表後建索引/觸發器），但跨 Schema 參考需手動排序。

版本演進與最佳實踐

• Oracle 10g+：支援 DBMS_METADATA_DIFF 比較兩資料庫結構差異
• Oracle 11g+：DBMS_METADATA.GET_DEPENDENT_DDL 取得相依物件 DDL
• Data Pump (EXPDP/IMPDP) 內建 METADATA_ONLY 參數，大量遷移首選

總結

DBMS_METADATA 徹底解決 DDL 提取難題，從「寫腳本的工匠」變為「呼叫 API 的架構師」。掌握此工具，Schema 遷移、版本控管、文件產出效率將指數級提升。