DBMS_METADATA 取得物件 DDL 完整指南

簡介

Oracle 9i 引入 DBMS_METADATA 套件，可程式化擷取任意物件的建立 DDL，支援篩選、轉換、美化輸出，是遷移、備份、文件化的利器。

基本用法

-- 設定長字串顯示
SET LONG 20000
SET PAGES 0

-- 取得單一表 DDL
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') FROM DUAL;

-- 取得 Schema 所有表 DDL
SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME, 'HR')
FROM   USER_TABLES;

進階：完整相依物件

使用 SET_TRANSFORM_PARAM 控制輸出細節：

BEGIN
  -- 開啟儲存空間、分區、授權、觸發器、索引等
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PARTITIONING', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'GRANTS', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TRIGGERS', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'INDEXES', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE); -- 格式化
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE); -- 結尾分號
END;
/

-- 然後查詢 GET_DDL 即含完整相依物件

常用物件類型代碼

批次匯出 Schema 所有物件

DECLARE
  h NUMBER := DBMS_METADATA.OPEN('SCHEMA_EXPORT');
BEGIN
  DBMS_METADATA.SET_FILTER(h, 'SCHEMA', 'HR');
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
  
  LOOP
    EXIT WHEN DBMS_METADATA.FETCH_CLOB(h) IS NULL;
  END LOOP;
  DBMS_METADATA.CLOSE(h);
END;
/

篩選特定物件

-- 僅匯出特定表
DBMS_METADATA.SET_FILTER(h, 'NAME_EXPR', 'IN (''EMPLOYEES'', ''DEPARTMENTS'')');

-- 排除系統索引
DBMS_METADATA.SET_FILTER(h, 'EXCLUDE_PATH_EXPR', 'INDEX/STATISTICS');

輸出至檔案

SET FEEDBACK OFF
SET TRIMSPOOL ON
SPOOL hr_schema.sql
SELECT DBMS_METADATA.GET_DDL(object_type, object_name, 'HR')
FROM   ALL_OBJECTS
WHERE  OWNER = 'HR'
AND    OBJECT_TYPE IN ('TABLE','VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE','TRIGGER','INDEX')
ORDER BY OBJECT_TYPE, OBJECT_NAME;
SPOOL OFF