萬盛學電腦網

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

Oracle DBMS_METADATA.GET_DDL 完整使用指南:提取資料庫物件 DDL 的實務技巧

前言

在 Oracle 資料庫管理與開發工作中,DBMS_METADATA.GET_DDL 是一個極為實用的內建套件函數,能夠動態提取資料庫物件的建立腳本(DDL)。無論是進行結構遷移、版本控管、備份還是異地同步,掌握此工具的用法都能大幅提升效率。本文將系統整理其核心語法、常見物件類型的提取範例、Schema 級批量匯出腳本,以及表空間與使用者 DDL 的取得方式,並附上 SQL*Plus 顯示優化設定,供實務參考。

一、SQL*Plus 環境優化設定

在 SQL*Plus 中執行 GET_DDL 時,預設輸出常因行寬、分頁、截斷等問題導致 DDL 不完整或難以閱讀。建議先執行以下設定:

SET LINESIZE 200
SET PAGESIZE 0
SET LONG 99999
SET FEEDBACK OFF
SET ECHO OFF
  • LINESIZE 200:加寬單行顯示寬度,避免換行破壞 DDL 結構。
  • PAGESIZE 0:關閉分頁標題與換頁,輸出純內容。
  • LONG 99999:擴大 LONG 型別欄位顯示長度,防止 DDL 被截斷(預設僅 80 字元)。
  • FEEDBACK OFF / ECHO OFF:抑制執行回饋與指令回顯,乾淨輸出。

二、常見資料庫物件 DDL 提取

DBMS_METADATA.GET_DDL 基本語法為:

DBMS_METADATA.GET_DDL(object_type, name, schema)
  • object_type:物件類型(大寫),如 'TABLE'、'INDEX'、'VIEW'、'PROCEDURE'、'FUNCTION' 等。
  • name:物件名稱。
  • schema:物件擁有者(Schema),可選;若省略則為當前使用者。

2.1 單一物件提取範例

-- 表
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;

2.2 Schema 級批量匯出腳本

以下腳本可一次匯出指定 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 參數。

三、表空間 DDL 提取

3.1 單一表空間

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'TBS_NAME') FROM DUAL;

3.2 所有表空間

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', ts.tablespace_name)
FROM DBA_TABLESPACES ts;

此查詢將列出資料庫中所有表空間(含系統、暫時、UNDO 等)的建立腳本,適合架構文件化或異機重建參考。

四、使用者 DDL 提取

4.1 單一使用者

SELECT DBMS_METADATA.GET_DDL('USER', 'EPAY_USER') FROM DUAL;

包含建立使用者語句、預設/暫時表空間指定、配額、角色授予等完整定義。

4.2 所有使用者

SELECT DBMS_METADATA.GET_DDL('USER', u.username)
FROM DBA_USERS u;

可快速產生全資料庫使用者建立腳本,便於災難恢復或環境複刻。

五、進階提示與注意事項

  • 物件類型代碼:除上述常見類型外,尚支援 'SEQUENCE'、'TRIGGER'、'PACKAGE'、'TYPE'、'MATERIALIZED_VIEW'、'SYNONYM'、'DB_LINK' 等,完整清單請參考 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_* 視圖查詢權限;若僅查詢自有物件,則無額外授權需求。

六、實務應用場景

  1. 結構版本控管:定期匯出 DDL 存入 Git/SVN,追蹤結構變更歷程。
  2. 異機環境同步:開發/測試/生產環境結構一致性驗證與同步。
  3. 災難恢復準備:保留核心物件建立腳本,縮短 RTO(恢復時間目標)。
  4. 文件自動化產出:整合至 CI/CD 流程,自動生成資料庫設計文件。

結語

DBMS_METADATA.GET_DDL 是 Oracle DBA 與開發人員工具箱中的利器。善用本文提供的環境設定、查詢範例與進階參數,能讓 DDL 提取工作更精準、高效、可自動化。建議將常用腳本封裝為 Shell/Python 自動化工具,納入日常維運標準作業程序(SOP)中。

copyright © 萬盛學電腦網 all rights reserved