Oracle DBMS_METADATA 實戰：輕鬆取得建表、索引、完整 Schema DDL

核心函數

DBMS_METADATA.GET_DDL(object_type, name [, schema] [, version] [, model] [, transform])

• object_type：'TABLE', 'INDEX', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'SEQUENCE', 'TYPE', 'MATERIALIZED_VIEW', 'SYNONYM', 'CONSTRAINT', 'GRANT', 'TABLESPACE', 'USER', 'ROLE', 'PROFILE' 等
• name：物件名稱
• schema：擁有者 (預設當前使用者)
• 回傳 CLOB，含完整 DDL

單一物件 DDL 擷取

-- 設定輸出格式
SET LONG 90000
SET PAGESIZE 0
SET LINESIZE 2000
SET FEEDBACK OFF
SET ECHO OFF
SET HEADING OFF

-- 擷取表 DDL
SELECT DBMS_METADATA.GET_DDL('TABLE', 'SZT_PQSO2', 'SHQSYS') FROM DUAL;

-- 擷取索引 DDL
SELECT DBMS_METADATA.GET_DDL('INDEX', 'INDXX_PQZJYW', 'SHQSYS') FROM DUAL;

-- 擷取視圖 DDL
SELECT DBMS_METADATA.GET_DDL('VIEW', 'EMP_VIEW', 'HR') FROM DUAL;

-- 擷取程序 DDL
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'MY_PROC', 'SCOTT') FROM DUAL;

輸出範例

CREATE TABLE "SHQSYS"."SZT_PQSO2" 
(  "PQBH" VARCHAR2(32) NOT NULL ENABLE, 
   "ZJYW" NUMBER(10,0), 
   "CGSO" NUMBER(10,0) NOT NULL ENABLE, 
   "SOLS" VARCHAR2(17), 
   "SORQ" VARCHAR2(8), 
   "SOWR" VARCHAR2(8), 
   "SOCL" VARCHAR2(6), 
   "YWHM" VARCHAR2(10), 
   "YWLX" VARCHAR2(6) 
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA1";

完整 Schema DDL 匯出腳本

-- spool_ddl.sql
SET PAGESIZE 0
SET LONG 90000
SET FEEDBACK OFF
SET ECHO OFF
SET HEADING OFF
SET TRIMSPOOL ON

SPOOL schema_ddl.sql

-- 連線目標 Schema (可選)
-- CONNECT shqsys/shqsys@hawk1;

-- 1. 表 DDL (含約束、索引、授權)
SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name)
FROM user_tables u;

-- 2. 視圖 DDL
SELECT DBMS_METADATA.GET_DDL('VIEW', u.view_name)
FROM user_views u;

-- 3. 索引 DDL (表 DDL 已含索引，若需單獨管理)
SELECT DBMS_METADATA.GET_DDL('INDEX', u.index_name)
FROM user_indexes u
WHERE table_name NOT LIKE 'SYS_%';  -- 排除系統索引

-- 4. 序列 DDL
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', u.sequence_name)
FROM user_sequences u;

-- 5. 程序/函數/套件 DDL
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', u.object_name)
FROM user_objects u
WHERE object_type IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');

-- 6. 觸發器 DDL
SELECT DBMS_METADATA.GET_DDL('TRIGGER', u.object_name)
FROM user_objects u
WHERE object_type = 'TRIGGER';

-- 7. 類型 DDL
SELECT DBMS_METADATA.GET_DDL('TYPE', u.object_name)
FROM user_objects u
WHERE object_type = 'TYPE';

-- 8. 同義詞 DDL
SELECT DBMS_METADATA.GET_DDL('SYNONYM', u.synonym_name)
FROM user_synonyms u;

-- 9. 授權 DDL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', u.grantee)
FROM user_users u;  -- 或 DBA_USERS

SPOOL OFF

進階：轉換參數控制輸出

-- 移除儲存子句、表空間、段屬性 (便於異構遷移)
BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', TRUE);
END;
/

-- 恢復預設
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT');

常用轉換參數

外鍵順序處理 (重建時必須注意)

DDL 匯出順序不保證父表先於子表。重建前需排序：

-- 查詢參照關係決定建立順序
SELECT c.table_name AS child_table, p.table_name AS parent_table
FROM user_constraints c
JOIN user_constraints p ON c.r_constraint_name = p.constraint_name
WHERE c.constraint_type = 'R'
ORDER BY parent_table, child_table;

-- 拓撲排序建立順序 (無循環參照)
-- 先建立無外鍵表 → 有外鍵表

Data Pump 對比

常見問題