在 Oracle 資料庫管理與開發工作中,DBMS_METADATA.GET_DDL 是一個極為實用的內建套件函數,能夠動態提取資料庫物件的建立腳本(DDL)。無論是進行結構遷移、版本控管、備份還是異地同步,掌握此工具的用法都能大幅提升效率。本文將系統整理其核心語法、常見物件類型的提取範例、Schema 級批量匯出腳本,以及表空間與使用者 DDL 的取得方式,並附上 SQL*Plus 顯示優化設定,供實務參考。
在 SQL*Plus 中執行 GET_DDL 時,預設輸出常因行寬、分頁、截斷等問題導致 DDL 不完整或難以閱讀。建議先執行以下設定:
SET LINESIZE 200
SET PAGESIZE 0
SET LONG 99999
SET FEEDBACK OFF
SET ECHO OFF
DBMS_METADATA.GET_DDL 基本語法為:
DBMS_METADATA.GET_DDL(object_type, name, schema)
object_type:物件類型(大寫),如 'TABLE'、'INDEX'、'VIEW'、'PROCEDURE'、'FUNCTION' 等。name:物件名稱。schema:物件擁有者(Schema),可選;若省略則為當前使用者。-- 表
SELECT DBMS_METADATA.GET_DDL('TABLE', 'TABLE_NAME', 'TABLE_OWNER') FROM DUAL;
-- 索引
SELECT DBMS_METADATA.GET_DDL('INDEX', 'INDEX_NAME', 'INDEX_OWNER') FROM DUAL;
-- 視圖
SELECT DBMS_METADATA.GET_DDL('VIEW', 'VIEW_NAME', 'VIEW_OWNER') FROM DUAL;
-- 預存程序
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'PROCEDURE_NAME', 'PROCEDURE_OWNER') FROM DUAL;
-- 函數
SELECT DBMS_METADATA.GET_DDL('FUNCTION', 'FUNCTION_NAME', 'FUNCTION_OWNER') FROM DUAL;
以下腳本可一次匯出指定 Schema 下所有表、視圖、索引、程序與函數的 DDL,輸出導向 schema_ddl.sql:
SET PAGESIZE 0
SET LONG 90000
SET FEEDBACK OFF
SET ECHO OFF
SPOOL schema_ddl.sql
SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name, u.owner) FROM DBA_TABLES u;
SELECT DBMS_METADATA.GET_DDL('VIEW', u.view_name, u.owner) FROM DBA_VIEWS u;
SELECT DBMS_METADATA.GET_DDL('INDEX', u.index_name, u.owner) FROM DBA_INDEXES u;
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', u.object_name, u.owner) FROM DBA_OBJECTS u WHERE u.object_type = 'PROCEDURE';
SELECT DBMS_METADATA.GET_DDL('FUNCTION', u.object_name, u.owner) FROM DBA_OBJECTS u WHERE u.object_type = 'FUNCTION';
SPOOL OFF
說明:需具備 DBA_* 視圖查詢權限(通常由 DBA 執行)。若僅需匯出當前使用者物件,可改查 USER_TABLES、USER_VIEWS 等視圖並省略 owner 參數。
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'TBS_NAME') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', ts.tablespace_name)
FROM DBA_TABLESPACES ts;
此查詢將列出資料庫中所有表空間(含系統、暫時、UNDO 等)的建立腳本,適合架構文件化或異機重建參考。
SELECT DBMS_METADATA.GET_DDL('USER', 'EPAY_USER') FROM DUAL;
包含建立使用者語句、預設/暫時表空間指定、配額、角色授予等完整定義。
SELECT DBMS_METADATA.GET_DDL('USER', u.username)
FROM DBA_USERS u;
可快速產生全資料庫使用者建立腳本,便於災難恢復或環境複刻。
DBMS_METADATA 官方文件。DBMS_METADATA.SET_TRANSFORM_PARAM 調整輸出細節,例如:
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE); —— 移除儲存參數(INITIAL、NEXT 等),利於跨環境部署。DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE); —— 隱藏段屬性(TABLESPACE、PCTFREE 等)。DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE); —— 在每條 DDL 結尾加上分號。DBA_* 視圖並呼叫 GET_DDL 會消耗系統資源,建議在離峰時段執行,或針對特定 Schema 限制範圍。SELECT_CATALOG_ROLE 或對應的 DBA_* 視圖查詢權限;若僅查詢自有物件,則無額外授權需求。DBMS_METADATA.GET_DDL 是 Oracle DBA 與開發人員工具箱中的利器。善用本文提供的環境設定、查詢範例與進階參數,能讓 DDL 提取工作更精準、高效、可自動化。建議將常用腳本封裝為 Shell/Python 自動化工具,納入日常維運標準作業程序(SOP)中。