Oracle DBMS_METADATA.GET_DDL 完全指南：對象 DDL 提取、Schema 級導出與參數優化實戰

DBMS_METADATA.GET_DDL 是 Oracle 官方提供的元數據提取核心 API，支援提取表、索引、視圖、存儲過程、函數、表空間、用戶等所有對象的標準化 DDL。掌握其進階用法，能讓您輕鬆實現「Schema 級遷移」、「版本控制入庫」、「異構對比」等高階運維場景。本文匯總完整語法、參數優化技巧與實戰腳本。

一、核心語法與對象類型對照表

DBMS_METADATA.GET_DDL (
  object_type   IN VARCHAR2,  -- 對象類型
  name          IN VARCHAR2,  -- 對象名稱
  schema        IN VARCHAR2 DEFAULT NULL,  -- 模式名，NULL 表示當前用戶
  version       IN VARCHAR2 DEFAULT 'COMPATIBLE',  -- 目標版本
  model         IN VARCHAR2 DEFAULT 'ORACLE',  -- 模型類型
  transform     IN VARCHAR2 DEFAULT 'DDL'  -- 輸出格式
) RETURN CLOB;

常用對象類型常量

二、SQL*Plus 顯示優化：必備四大設置

不設置會導致輸出截斷、換行混亂、提示符干擾：

SET LONG 999999        -- CLOB 顯示長度（默認 80 字符）
SET PAGESIZE 0         -- 關閉分頁標題
SET FEEDBACK OFF       -- 關閉「X rows selected」
SET ECHO OFF           -- 關閉命令回顯
SET LINESIZE 32767     -- 最大行寬（防換行）
SET TRIMSPOOL ON       -- 去除尾部空格
SET TERMOUT OFF        -- 關閉終端輸出（僅寫文件時）

三、實戰腳本庫

1. 單對象提取範例

-- 表
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') FROM DUAL;

-- 索引
SELECT DBMS_METADATA.GET_DDL('INDEX', 'PK_EMP', 'SCOTT') FROM DUAL;

-- 視圖
SELECT DBMS_METADATA.GET_DDL('VIEW', 'EMP_VIEW', 'SCOTT') FROM DUAL;

-- 存儲過程
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'UPDATE_SAL', 'SCOTT') FROM DUAL;

-- 函數
SELECT DBMS_METADATA.GET_DDL('FUNCTION', 'CALC_BONUS', 'SCOTT') FROM DUAL;

-- 表空間
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'USERS') FROM DUAL;

-- 用戶 (含權限、角色、配額)
SELECT DBMS_METADATA.GET_DDL('USER', 'SCOTT') FROM DUAL;

2. Schema 級全量導出生成腳本

SET LONG 999999
SET PAGESIZE 0
SET FEEDBACK OFF
SET ECHO OFF
SET LINESIZE 32767
SET TRIMSPOOL ON
SET TERMOUT OFF

SPOOL schema_ddl.sql

PROMPT ===== TABLES =====
SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name, u.owner)
FROM DBA_TABLES u
WHERE u.owner = UPPER('&SCHEMA_NAME');

PROMPT ===== VIEWS =====
SELECT DBMS_METADATA.GET_DDL('VIEW', u.view_name, u.owner)
FROM DBA_VIEWS u
WHERE u.owner = UPPER('&SCHEMA_NAME');

PROMPT ===== INDEXES =====
SELECT DBMS_METADATA.GET_DDL('INDEX', u.index_name, u.owner)
FROM DBA_INDEXES u
WHERE u.table_owner = UPPER('&SCHEMA_NAME');

PROMPT ===== PROCEDURES =====
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', u.object_name, u.owner)
FROM DBA_OBJECTS u
WHERE u.owner = UPPER('&SCHEMA_NAME')
AND u.object_type = 'PROCEDURE';

PROMPT ===== FUNCTIONS =====
SELECT DBMS_METADATA.GET_DDL('FUNCTION', u.object_name, u.owner)
FROM DBA_OBJECTS u
WHERE u.owner = UPPER('&SCHEMA_NAME')
AND u.object_type = 'FUNCTION';

PROMPT ===== PACKAGES =====
SELECT DBMS_METADATA.GET_DDL('PACKAGE', u.object_name, u.owner)
FROM DBA_OBJECTS u
WHERE u.owner = UPPER('&SCHEMA_NAME')
AND u.object_type = 'PACKAGE';

PROMPT ===== TRIGGERS =====
SELECT DBMS_METADATA.GET_DDL('TRIGGER', u.trigger_name, u.owner)
FROM DBA_TRIGGERS u
WHERE u.owner = UPPER('&SCHEMA_NAME');

PROMPT ===== SEQUENCES =====
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', u.sequence_name, u.sequence_owner)
FROM DBA_SEQUENCES u
WHERE u.sequence_owner = UPPER('&SCHEMA_NAME');

PROMPT ===== SYNONYMS =====
SELECT DBMS_METADATA.GET_DDL('SYNONYM', u.synonym_name, u.owner)
FROM DBA_SYNONYMS u
WHERE u.owner = UPPER('&SCHEMA_NAME');

SPOOL OFF
SET TERMOUT ON

3. 表空間與用戶全量導出

-- 所有表空間
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', ts.tablespace_name)
FROM DBA_TABLESPACES ts;

-- 所有用戶
SELECT DBMS_METADATA.GET_DDL('USER', u.username)
FROM DBA_USERS u;

四、進階轉換參數：定制化輸出

通過 DBMS_METADATA.SET_TRANSFORM_PARAM 控制輸出細節：

-- 1. 去除 STORAGE 子句（遷移時避免原存儲參數干擾）
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);

-- 2. 去除 TABLESPACE 子句（異構遷移必備）
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);

-- 3. 去除 SEGMENT ATTRIBUTES (PCTFREE, INITRANS 等)
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

-- 4. 美化格式 (SQLTERMINATOR, PRETTY)
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);

-- 5. 包含授權 (GRANT)
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'GRANTS', TRUE);

-- 重置所有參數
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT');

五、異構遷移實戰模板：Oracle → PostgreSQL/MySQL

-- 1. 設置去除 Oracle 特有子句
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, 'PARTITIONING', FALSE);  -- 去除分區語法

-- 2. 提取純結構 DDL
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner)
FROM DBA_TABLES
WHERE owner = 'TARGET_SCHEMA';

-- 3. 後處理：正則替換數據類型、去除雙引號、調整自增語法等
-- 建議配合 Python/Shell 腳本自動化完成

六、性能優化與注意事項

• 大對象提取慢：分區表、大視圖 DDL 生成耗時，建議並行提取或分批次
• 權限要求：需 SELECT_CATALOG_ROLE 或 DBA 權限訪問 DBA_* 視圖
• 版本兼容：version => '12.2' 可生成指定版本兼容語法
• 依賴順序：導入時須按類型順序：Type → Table → Index → Constraint → View → Proc/Func → Trigger → Synonym → Grant
• 加密對象：包含 WRAPPED 關鍵字，無法還原源碼

七、總結

DBMS_METADATA.GET_DDL 是 Oracle 元數據操作的「瑞士軍刀」。掌握 對象類型常量、顯示優化參數、轉換屬性控制、Schema 級導出腳本 四大核心，您就能應對 99% 的 DDL 提取、遷移、版本控制、異構對比場景。建議將本文腳本庫納入團隊運維標準工具箱，標準化元數據管理流程。