Oracle 9i+ 利用 DBMS_METADATA 秒級取得建表、索引、Schema 完整 DDL 指令

痛點回顧

9i 之前取得 DDL 僅能：EXP ROWS=N (格式難直接用) 或撰寫複雜 Data Dictionary 查詢腳本 (IOT、Nested Table 等複雜物件常遺漏)。

DBMS_METADATA 核心 API

1. 取得單一物件 DDL

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

-- 索引
SELECT DBMS_METADATA.GET_DDL('INDEX', 'EMP_NAME_IX', 'HR') FROM DUAL;

-- 物件類型代碼參考：TABLE, INDEX, VIEW, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, TYPE, TRIGGER, SYNONYM, CONSTRAINT 等

輸出為 CLOB，含完整 CREATE 陳述式、儲存子句、平行度等，**加上分號即可直接執行**。

2. 取得整個 Schema DDL (依相依順序)

-- 開啟轉換參數 (可選)
BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);      -- 移除 STORAGE 子句
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);   -- 移除 TABLESPACE
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);        -- 格式化縮排
END;
/

-- 取得 Schema 所有物件 DDL
SET LONG 1000000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)
FROM dba_objects
WHERE owner = 'HR'
  AND object_type IN ('TABLE','INDEX','VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE','TRIGGER','TYPE','SYNONYM')
ORDER BY 
  CASE object_type 
    WHEN 'TYPE' THEN 1           -- 先建型別
    WHEN 'TABLE' THEN 2          -- 再建表
    WHEN 'INDEX' THEN 3          -- 再建索引
    WHEN 'CONSTRAINT' THEN 4     -- 再建約束 (含 FK)
    ELSE 5 END,
  object_name;

3. 處理外鍵相依順序 (關鍵)

DDL 執行順序必須滿足：父表先於子表。可查詢 DBA_CONSTRAINTS + DBA_CONS_COLUMNS 建立相依圖，或使用 DBMS_METADATA.GET_DEPENDENT_DDL：

-- 取得指定表的所有相依 DDL (含索引、約束、授權、觸發器等)
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('TABLE', 'EMPLOYEES', 'HR') FROM DUAL;

進階轉換參數 (SET_TRANSFORM_PARAM)

實戰：跨環境 Schema 遷移腳本範本

-- 1. 來源端產出 DDL 檔案
SPOOL hr_schema_ddl.sql
BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);
END;
/
SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)
FROM dba_objects
WHERE owner = 'HR'
  AND object_type IN ('TYPE','TABLE','VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE','TRIGGER','SYNONYM')
ORDER BY DECODE(object_type,'TYPE',1,'TABLE',2,'VIEW',3,'SEQUENCE',4,'PROCEDURE',5,'FUNCTION',6,'PACKAGE',7,'TRIGGER',8,'SYNONYM',9);
SPOOL OFF

-- 2. 目標端執行前建立使用者、表空間、授權
-- 3. 執行 hr_schema_ddl.sql (注意外鍵順序，必要時手動調整)
-- 4. 使用 Data Pump (impdp) 導入資料

結語

DBMS_METADATA 是 9i 引入的里程碑功能，將「取得 DDL」從痛苦的腳本撰寫變為簡單的 SQL 呼叫。善用轉換參數淨化輸出、配合 GET_DEPENDENT_DDL 處理相依順序，能構建穩健的自動化 Schema 遷移流程。