Oracle 9i 輕鬆獲取建表與索引 DDL：DBMS_METADATA 實戰指南

痛點回顧

9i 之前獲取 DDL 十分繁瑣：EXP ROWS=N 導出格式難直接執行；查詢數據字典寫腳本複雜，對 IOT、嵌套表等復雜對象支持不足。

Oracle 9i 解決方案：DBMS_METADATA 包

DBMS_METADATA.GET_DDL 可直接從數據字典提取任意對象的完整 DDL，支持單個對象或整個 Schema，使用極其簡單。

單個對象 DDL 獲取步驟

SET HEADING OFF
SET ECHO OFF
SET PAGES 999
SET LONG 90000
SPOOL get_single.sql

SELECT DBMS_METADATA.GET_DDL('TABLE', 'SZT_PQSO2', 'SHQSYS') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX', 'INDXX_PQZJYW', 'SHQSYS') FROM DUAL;

SPOOL OFF

輸出示例

CREATE TABLE "SHQSYS"."SZT_PQSO2"
( "PQBH" VARCHAR2(32) NOT NULL ENABLE,
  "ZJYW" NUMBER(10,0),
  "CGSO" NUMBER(10,0) NOT NULL ENABLE,
  "SOLS" VARCHAR2(17),
  "SORQ" VARCHAR2(8),
  "SOWR" VARCHAR2(8),
  "SOCL" VARCHAR2(6),
  "YWHM" VARCHAR2(10),
  "YWLX" VARCHAR2(6)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA1";

CREATE INDEX "SHQSYS"."INDXX_PQZJYW" ON "SHQSYS"."SZT_PQSO2" ("ZJYW")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA1";

輸出已包含完整存儲子句、表空間、約束狀態，只需在末尾加分號即可直接執行重建。

進階用法

獲取整個 Schema 的 DDL

SET LONG 2000000
SPOOL schema_ddl.sql
SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name, u.owner)
FROM user_tables u;
-- 同理 INDEX、VIEW、SEQUENCE、TRIGGER 等
SPOOL OFF

轉換屬性（REMAP）

-- 導出時自動將表空間從 PROD_DATA 改為 TEST_DATA
SELECT DBMS_METADATA.GET_DDL('TABLE', 'MY_TABLE', 'MY_SCHEMA')
FROM DUAL;
-- 配合 DBMS_METADATA.SET_TRANSFORM_PARAM 實現 REMAP_TABLESPACE

過濾系統生成名稱（如 SYS_PK_...）

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
-- 關閉存儲子句輸出，獲得更簡潔 DDL

最佳實踐建議

1. 設置足夠的 LONG：SET LONG 2000000 防止大對象 DDL 截斷。
2. 關閉 HEADING/ECHO：獲得純淨 SQL 輸出。
3. 使用 SPOOL 導出文件：便於版本控制、異構遷移、災備演練。
4. 定期備份 DDL：納入變更管理流程，Schema 變更前後各導出一次，做差異對比。

版本兼容性

• Oracle 9i：引入 DBMS_METADATA。
• 10g+：增強 SET_TRANSFORM_PARAM、OPEN/CONVERT/FETCH 接口，支持異構遷移、並行提取。
• 12c+：DBMS_METADATA_DIFF 比較兩個對象/Schema 的差異。