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

DBMS_METADATA.GET_DDL：一行指令輕鬆提取表、索引、程序完整 DDL 定義

編輯：Oracle 資料庫基礎

痛點：傳統 DDL 提取的困境

手工維護 DDL 原始碼庫，版本同步極其痛苦
工具導出格式不一、遺漏儲存子句、參數不全
跨環境遷移時，目標端參數（表空間、儲存參數）需手動調整

Oracle 9i 解決方案：DBMS_METADATA.GET_DDL

內建套件函數，直接從資料字典提取**當前生效**的完整 DDL，支援所有物件類型。

基本語法


DBMS_METADATA.GET_DDL(
  object_type  IN VARCHAR2,  -- 物件類型：TABLE, INDEX, PROCEDURE, FUNCTION, PACKAGE, TRIGGER, VIEW...
  name         IN VARCHAR2,  -- 物件名稱
  schema       IN VARCHAR2 DEFAULT NULL,  -- 擁有者，預設當前用戶
  version      IN VARCHAR2 DEFAULT 'COMPATIBLE',  -- 目標版本相容性
  model        IN VARCHAR2 DEFAULT 'ORACLE',  -- 輸出模型
  transform    IN VARCHAR2 DEFAULT 'DDL'    -- 轉換類型：DDL, SXML, MODIFY
) RETURN CLOB;

實戰範例：提取 SALES 表完整 DDL


SET LONG 90000
SET LONGCHUNKSIZE 90000
SET PAGESIZE 0
SET LINESIZE 32767
SET FEEDBACK OFF
SET ECHO OFF
SET TRIMSPOOL ON

SPOOL sales_table_ddl.sql
SELECT DBMS_METADATA.GET_DDL('TABLE', 'SALES') FROM DUAL;
SPOOL OFF

進階應用場景

場景	指令範例
提取特定 Schema 所有表 DDL	`SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, 'SCOTT') FROM user_tables;`
提取索引 DDL（含儲存參數）	`SELECT DBMS_METADATA.GET_DDL('INDEX', 'PK_EMP') FROM DUAL;`
提取程序/套件完整原始碼	`SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'MY_PROC') FROM DUAL;`
跨版本相容輸出（10g→11g）	`DBMS_METADATA.GET_DDL('TABLE', 'T', 'SCOTT', '11.2.0')`
只輸出建表核心、排除儲存/表空間	`DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);`
輸出 SXML 格式供程式解析	`DBMS_METADATA.GET_DDL('TABLE', 'T', 'SCOTT', 'COMPATIBLE', 'ORACLE', 'SXML')`

關鍵設定參數


-- 移除儲存子句（跨環境遷移必用）
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

-- 保留物理屬性（同平台克隆）
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', TRUE);

自動化腳本範例：批量導出 Schema 所有物件


BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);
  
  FOR obj IN (SELECT object_type, object_name FROM user_objects WHERE object_type IN ('TABLE','INDEX','VIEW','PROCEDURE','FUNCTION','PACKAGE','TRIGGER','SEQUENCE','SYNONYM')) LOOP
    DECLARE
      v_ddl CLOB;
      v_file UTL_FILE.FILE_TYPE;
    BEGIN
      v_ddl := DBMS_METADATA.GET_DDL(obj.object_type, obj.object_name);
      v_file := UTL_FILE.FOPEN('DDL_DIR', obj.object_type || '_' || obj.object_name || '.sql', 'W', 32767);
      UTL_FILE.PUT(v_file, v_ddl);
      UTL_FILE.FCLOSE(v_file);
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Failed: ' || obj.object_type || '.' || obj.object_name || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/