善用 DBMS_METADATA 套件輕鬆取得物件 DDL

自 Oracle 9i 起，Oracle 內建強大的 DBMS_METADATA 套件，讓 DBA 與開發者能以程式化方式擷取資料庫物件的建立 DDL 語句，免去手工拼湊的麻煩。本文示範核心用法與進階應用。

基本用法：取得單一物件 DDL

核心函數 GET_DDL 接受物件類型、名稱與結構描述等參數，回傳 CLOB 格式的 DDL：

SET LONG 2000
SELECT DBMS_METADATA.GET_DDL('TABLE', 'TEST') FROM DUAL;

輸出包含完整的 CREATE TABLE 語句，含儲存參數、表空間定義等細節。

進階應用：取得完整相依物件 DDL

單一物件往往牽涉索引、授權、觸發器等相依物件。Oracle 提供示範腳本 $ORACLE_HOME/rdbms/demo/mddemo.sql，展示如何透過 ADD_TRANSFORM 等介面，一次擷取完整的物件群組 DDL，包含：

• 索引、條件約束、授權
• 觸發器、同義詞、檢視
• 資料表分區、LOB 區段等儲存屬性

常見轉換參數

實務範例：匯出結構描述所有表格 DDL

DECLARE
  h NUMBER;
  th NUMBER;
  doc CLOB;
BEGIN
  h := DBMS_METADATA.OPEN('TABLE');
  DBMS_METADATA.SET_FILTER(h, 'SCHEMA', 'SCOTT');
  th := DBMS_METADATA.ADD_TRANSFORM(h, 'DDL');
  DBMS_METADATA.SET_TRANSFORM_PARAM(th, 'PRETTY', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(th, 'SEGMENT_ATTRIBUTES', FALSE);
  LOOP
    doc := DBMS_METADATA.FETCH_CLOB(h);
    EXIT WHEN doc IS NULL;
    DBMS_OUTPUT.PUT_LINE(doc);
  END LOOP;
  DBMS_METADATA.CLOSE(h);
END;
/

注意事項

• 需具備 SELECT_CATALOG_ROLE 或同等權限
• 大量物件匯出時，建議分批處理，避免 CLOB 過大導致效能問題
• 跨版本匯入時，需注意語法相容性（如分區語法差異）

DBMS_METADATA 是資料庫版本控管、環境遷移、文件產生的利器，善用它能大幅提升 DBA 作業效率與準確度。