Oracle 9i DBMS_METADATA 包提取 DDL 實戰：單對象、全 Schema、存儲過程與 DEMO 腳本

一、核心函數

DBMS_METADATA.GET_DDL(
  object_type VARCHAR2,  -- 对象类型：TABLE, INDEX, PROCEDURE, TRIGGER, VIEW 等
  name        VARCHAR2,  -- 对象名
  schema      VARCHAR2   -- 模式名（可选，默认当前用户）
) RETURN CLOB;

二、提取單個對象 DDL

SET LONG 20000
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL dept.sql
SELECT DBMS_METADATA.GET_DDL('TABLE', 'DEPT', 'SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX', 'DEPT_IDX', 'SCOTT') FROM DUAL;
SPOOL OFF

三、提取整個 Schema 所有表/索引

CONNECT scott/tiger
SPOOL scott_schema.sql
SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name) FROM user_tables u;
SELECT DBMS_METADATA.GET_DDL('INDEX', u.index_name) FROM user_indexes u;
SPOOL OFF

四、提取所有存儲過程/函數/包

CONNECT brucelau/brucelau
SPOOL procedures.sql
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', u.object_name)
FROM user_objects u WHERE object_type = 'PROCEDURE';
SPOOL OFF

五、進階轉換參數

• DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE); —— 去除 STORAGE 子句；
• DBMS_METADATA.SET_TRANSFORM_PARAM(..., 'SEGMENT_ATTRIBUTES', FALSE); —— 去除 TABLESPACE/LOGGING 等；
• DBMS_METADATA.SET_TRANSFORM_PARAM(..., 'SQLTERMINATOR', TRUE); —— 自動添加分號。

六、官方 DEMO 腳本

@$ORACLE_HOME/rdbms/demo/mddemo.sql

涵蓋：表、索引、約束、授權、觸發器、類型、同義詞、資源消費組等所有對象類型的 DDL 提取範例。

七、輸出示例

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TEST') FROM DUAL;
CREATE TABLE "SYS"."TEST"
( "OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), ... )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"