Oracle 物件定義反查指南：DBMS_METADATA.GET_DDL 取得 TABLE/INDEX/VIEW/SEQUENCE/PACKAGE/PROCEDURE/TRIGGER 完整 DDL

痛點

開發/維護中頻繁需求：「這張表怎麼建的？」、「這個索引欄位順序？」、「Package 規格長什麼樣？」。MySQL 有 SHOW CREATE TABLE，Oracle 靠 DBMS_METADATA.GET_DDL。

核心函數

DBMS_METADATA.GET_DDL (
  object_type IN VARCHAR2,  -- 物件類型
  name        IN VARCHAR2,  -- 物件名稱
  schema      IN VARCHAR2 DEFAULT NULL,  -- Schema，預設當前用戶
  version     IN VARCHAR2 DEFAULT 'COMPATIBLE',
  model       IN VARCHAR2 DEFAULT 'ORACLE',
  transform   IN VARCHAR2 DEFAULT 'DDL'
) RETURN CLOB;

常用物件類型代碼

實戰查詢範例

1. 查看當前用戶所有物件清單

SELECT object_name, object_type FROM user_objects ORDER BY object_type, object_name;

2. 取得特定表完整 DDL

SELECT DBMS_METADATA.GET_DDL('TABLE', 'TB_STUDENT', 'JELLY') FROM dual;

3. 批次匯出某 Schema 所有表 DDL

SET LONG 1000000
SET PAGESIZE 0
SPOOL schema_ddl.sql
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, 'JELLY') FROM user_tables;
SPOOL OFF

4. 取得索引/視圖/序列/觸發器 DDL

SELECT DBMS_METADATA.GET_DDL('INDEX', 'STUDENTID_INDEX', 'JELLY') FROM dual;
SELECT DBMS_METADATA.GET_DDL('VIEW', 'V_TB_STUDENT', 'JELLY') FROM dual;
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', 'TEST_SEQ', 'JELLY') FROM dual;
SELECT DBMS_METADATA.GET_DDL('TRIGGER', trigger_name) FROM user_triggers;

5. 以 DBA 身分查詢任意 Schema 物件

SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner)
FROM dba_tables WHERE owner = 'SCOTT';

進階：SET_TRANSFORM_PARAM 客製化輸出

BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);       -- 移除 STORAGE 子句
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);    -- 移除 TABLESPACE
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);         -- 美化縮排
END;
/
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP') FROM dual;

權限需求

• 查詢自身 Schema 物件：需 SELECT_CATALOG_ROLE 或物件擁有者權限。
• 查詢他 Schema 物件：需 SELECT ANY DICTIONARY 或 DBA 權限。

總結

Oracle 物件定義反查核心 = DBMS_METADATA.GET_DDL(object_type, name, schema)。搭配 USER_*/DBA_* 視圖列舉物件，配合 SET_TRANSFORM_PARAM 產出乾淨可移植 DDL，是 DBA/開發者日常必備技能。