萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle DBMS_METADATA 套件實戰:提取 DDL、索引、授權、觸發器完整腳本

DBMS_METADATA 簡介

Oracle 9i 引入 DBMS_METADATA 套件,可程式化提取物件建立 DDL,支援 TABLE、INDEX、VIEW、TRIGGER、GRANT、TYPE 等所有物件類型,功能極強大,是遷移、備份、版控的利器。

核心函數

  • GET_DDL(object_type, name, schema, version, model, transform) → 回傳 CLOB
  • GET_DEPENDENT_DDL(object_type, base_object_name, base_schema) → 相依物件 DDL (索引、觸發器、授權)
  • GET_GRANTED_DDL(object_type, grantee) → 角色/系統/物件授權
  • ADD_TRANSFORM(handle, transform_name) → 套用轉換(如 PRETTY、SQLTERMINATOR、SEGMENT_ATTRIBUTES)

基本用法:提取單一表 DDL


SET LONG 20000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DDL('TABLE', 'TEST', 'SYS') FROM dual;

輸出完整 CREATE TABLE 含儲存參數、表空間、約束條件。

進階:一次提取 Schema 所有物件


-- 1. 開啟轉換控制代碼
DECLARE
    h NUMBER;
BEGIN
    h := DBMS_METADATA.OPEN('TABLE');
    DBMS_METADATA.SET_FILTER(h, 'SCHEMA', 'HR');
    DBMS_METADATA.SET_TRANSFORM_PARAM(h, 'PRETTY', TRUE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(h, 'SQLTERMINATOR', TRUE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(h, 'SEGMENT_ATTRIBUTES', FALSE);  -- 排除儲存子句
    DBMS_METADATA.SET_TRANSFORM_PARAM(h, 'STORAGE', FALSE);
    
    -- 迴圈輸出
    FOR cur IN (SELECT table_name FROM user_tables) LOOP
        DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.FETCH_CLOB(h));
    END LOOP;
    DBMS_METADATA.CLOSE(h);
END;
/

官方示範腳本

路徑:$ORACLE_HOME/rdbms/demo/mddemo.sql

涵蓋:TABLE、INDEX、CONSTRAINT、TRIGGER、GRANT、AUDIT、COMMENT、MATERIALIZED VIEW、TYPE、SYNONYM、SEQUENCE 等完整 DDL 提取範例。

常用 Transform 參數

參數值效果
PRETTYTRUE/FALSE格式化縮排、換行
SQLTERMINATORTRUE/FALSE結尾加分號
SEGMENT_ATTRIBUTESTRUE/FALSE包含 TABLESPACE、STORAGE、PCTFREE 等
STORAGETRUE/FALSE包含 STORAGE 子句
CONSTRAINTSTRUE/FALSE包含約束條件
REF_CONSTRAINTSTRUE/FALSE包含外鍵
FORCETRUE/FALSE強制建立 (IF NOT EXISTS)
INHERITTRUE/FALSE繼承父表屬性 (Partition)

實用場景

  1. Schema 遷移: 開發→測試→生產,提取 DDL 版控、比對差異
  2. 災難恢復: 定期 expdp + DBMS_METADATA 完整備份物件定義
  3. 文檔生成: 自動產生資料字典文檔
  4. 版本升級驗證: 對比升級前後物件定義差異

注意事項

  • 需 SELECT_CATALOG_ROLE 或 DBA 權限查詢其他 Schema
  • 大量物件提取時注意 SET LONG、SET LONGCHUNKSIZE 避免截斷
  • 12c+ 多租戶環境需在 CDB/PDB 正確容器執行
copyright © 萬盛學電腦網 all rights reserved