您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle DBMS_METADATA 實戰：提取、儲存與重構資料庫物件 DDL 完整解決方案

編輯：Oracle 資料庫基礎

痛點：傳統腳本提取 DDL 的三大缺陷

不完整：無法覆蓋分區、函數索引、ASSM、壓縮、In-Memory 等新特性選項
過時失效：版本升級後資料字典視圖變更，腳本報錯或產生錯誤 DDL
客戶端侷限：SQL*Plus SPOOL 產生文字檔，無法在 Server 端 PL/SQL 直接使用

解決方案：DBMS_METADATA 官方 API (9.2+)

核心函數：DBMS_METADATA.GET_DDL(object_type, name, schema) 回傳 CLOB，支援完整物件屬性、儲存參數、表空間、並行度、日誌模式等。

實戰案例：大量刪除資料的高效能重建流程

建立同結構暫存表 target_x (無 PK/UK/FK/索引)
INSERT /*+ APPEND */ INTO target_x SELECT ... FROM target WHERE keep_condition
暫存表建立索引 (NOLOGGING PARALLEL)
暫存表建立約束
RENAME target TO target_old; RENAME target_x TO target;
驗證無誤後 DROP TABLE target_old PURGE

關鍵需求：在 PL/SQL 中動態提取原表的索引、約束、觸發器 DDL，按正確順序重建。

MetaDataPkg 完整實作：企業級元數據快取框架

核心資料結構

TYPE tMetaObject IS RECORD (
  aName       VARCHAR2(30),   -- 物件名
  aType       VARCHAR2(30),   -- 類型: YES/NO(分區), UNIQUE/NONUNIQUE, P/U/C/R(約束)
  aLogging    VARCHAR2(30),   -- LOGGING/NOLOGGING
  aParallel   VARCHAR2(30),   -- 並行度
  aStatus     VARCHAR2(30),   -- VALID/UNUSABLE, Y/N
  aValidated  VARCHAR2(30),   -- VALIDATED/NOT VALIDATED
  aRely       VARCHAR2(30),   -- RELY/NORELY
  aDDLString  VARCHAR2(32767) -- 完整 DDL
);

TYPE tFullMetaObject IS RECORD (
  aTable       tMetaObject,
  aIndexes     tArrayMetaObject,
  aConstraints tArrayMetaObject,
  aTriggers    tArrayMetaObject
);

TYPE tArrayFullMetaObjectByString IS TABLE OF tFullMetaObject INDEX BY VARCHAR2(30);

關鍵環境設定 (SetEnvironment)

DBMS_METADATA.SET_TRANSFORM_PARAM(session_transform, 'PRETTY',          FALSE);  -- 無縮排，緊湊
DBMS_METADATA.SET_TRANSFORM_PARAM(session_transform, 'SEGMENT_ATTRIBUTES', TRUE);  -- 物理屬性
DBMS_METADATA.SET_TRANSFORM_PARAM(session_transform, 'STORAGE',          TRUE);  -- 儲存子句
DBMS_METADATA.SET_TRANSFORM_PARAM(session_transform, 'TABLESPACE',       TRUE);  -- 表空間
DBMS_METADATA.SET_TRANSFORM_PARAM(session_transform, 'CONSTRAINTS',      FALSE); -- 表 DDL 不含約束
DBMS_METADATA.SET_TRANSFORM_PARAM(session_transform, 'REF_CONSTRAINTS',  FALSE); -- 不含 FK
DBMS_METADATA.SET_TRANSFORM_PARAM(session_transform, 'CONSTRAINTS_AS_ALTER', FALSE); -- 不產生 ALTER TABLE

設計哲學：將表、索引、約束、觸發器 DDL 完全解耦，賦予呼叫端彈性控制建立順序 (如先建表、再平行建索引、最後啟用約束)。

核心提取邏輯 (GetDDL)

FUNCTION GetDDL(pName VARCHAR2, pType VARCHAR2) RETURN VARCHAR2 IS
  vClob CLOB; vLen PLS_INTEGER; vOffset PLS_INTEGER := 1; vChunk VARCHAR2(32767);
BEGIN
  vClob := DBMS_METADATA.GET_DDL(pType, UPPER(pName));
  vLen  := DBMS_LOB.GETLENGTH(vClob);
  -- 分塊讀取超長 DDL (分區表可能 > 32K)
  FOR i IN 1..CEIL(vLen/32767) LOOP
    DBMS_LOB.READ(vClob, LEAST(32767, vLen-(i-1)*32767), vOffset, vChunk);
    vChunk := REPLACE(vChunk, '"'||USER||'"\.', '');  -- 移除 Schema 前綴
    vChunk := LTRIM(RTRIM(REPLACE(vChunk, CHR(10), '')));
    vOffset := vOffset + 32767;
  END LOOP;
  IF DBMS_LOB.ISOPEN(vClob) > 0 THEN DBMS_LOB.CLOSE(vClob); END IF;
  RETURN vChunk;  -- 簡化版僅回傳第一塊，生產環境需組裝完整 CLOB
END;

智能快取機制 (Load / GetMeta / Reset)

Load(pTable, pForce)：首次從字典提取並快取；pForce=TRUE 強制重載
GetMeta(pTable, pForce)：取得完整 tFullMetaObject，支援快取命中
Reset(pTable) / Reset：清除指定/所有快取
Show(pTable) / Show：格式化輸出快取內容 (除錯用)

使用範例：一鍵列印 EMPTEST 完整元數據

BEGIN
  MetaDataPkg.Load('EMPTEST', TRUE);
  MetaDataPkg.Show;
END;
/*
輸出範例：
Start Full Object: EMPTEST
Start Table: EMPTEST
*** Name: EMPTEST Type: NO Logging: LOGGING Parallel: 1 Status: Y ***
DDL String: CREATE TABLE "EMPTEST" ( "EMPNO" NUMBER(*,0) NOT NULL ENABLE, ... ) TABLESPACE "TOOLS"
***
Finish Table: EMPTEST
Start Indexes: EMPTEST
*** Name: EMPTEST_PK Type: UNIQUE Logging: LOGGING Parallel: 1 Status: VALID ***
DDL String: CREATE UNIQUE INDEX "EMPTEST_PK" ON "EMPTEST" ("EMPNO") ...
***
Finish Indexes: EMPTEST
Start Constraints: EMPTEST
*** Name: EMPTEST_PK Type: P Status: ENABLED Validated: VALIDATED Rely: NORELY ***
DDL String: ALTER TABLE "EMPTEST" ADD CONSTRAINT "EMPTEST_PK" PRIMARY KEY ("EMPTO") ...
***
Finish Constraints: EMPTEST
Finish Full Object: EMPTEST
*/

進階應用場景

場景	實作要點
跨 Schema 物件遷移	`GET_DDL(..., 'SCHEMA2')` + 移除 Schema 前綴 + 目標 Schema 重建
版本升級前備份 DDL	排程遍歷 `USER_OBJECTS`，彈性提取所有物件 DDL 存入版本庫
自動化測試環境建立	生產庫提取核心表 DDL → 修改表空間/儲存參數 → 測試庫一鍵部署
DDL 變更稽核	定期 `GET_DDL` 比對檢查和，偵測未授權變更
異構遷移 (Oracle→PostgreSQL)	提取 DDL → 解析 AST → 轉譯目標方言 (需配合工具如 Ora2Pg)