Oracle 9i 引入 DBMS_METADATA 套件,可程式化提取物件建立 DDL,支援 TABLE、INDEX、VIEW、TRIGGER、GRANT、TYPE 等所有物件類型,功能極強大,是遷移、備份、版控的利器。
GET_DDL(object_type, name, schema, version, model, transform) → 回傳 CLOBGET_DEPENDENT_DDL(object_type, base_object_name, base_schema) → 相依物件 DDL (索引、觸發器、授權)GET_GRANTED_DDL(object_type, grantee) → 角色/系統/物件授權ADD_TRANSFORM(handle, transform_name) → 套用轉換(如 PRETTY、SQLTERMINATOR、SEGMENT_ATTRIBUTES)
SET LONG 20000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DDL('TABLE', 'TEST', 'SYS') FROM dual;
輸出完整 CREATE TABLE 含儲存參數、表空間、約束條件。
-- 1. 開啟轉換控制代碼
DECLARE
h NUMBER;
BEGIN
h := DBMS_METADATA.OPEN('TABLE');
DBMS_METADATA.SET_FILTER(h, 'SCHEMA', 'HR');
DBMS_METADATA.SET_TRANSFORM_PARAM(h, 'PRETTY', TRUE);
DBMS_METADATA.SET_TRANSFORM_PARAM(h, 'SQLTERMINATOR', TRUE);
DBMS_METADATA.SET_TRANSFORM_PARAM(h, 'SEGMENT_ATTRIBUTES', FALSE); -- 排除儲存子句
DBMS_METADATA.SET_TRANSFORM_PARAM(h, 'STORAGE', FALSE);
-- 迴圈輸出
FOR cur IN (SELECT table_name FROM user_tables) LOOP
DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.FETCH_CLOB(h));
END LOOP;
DBMS_METADATA.CLOSE(h);
END;
/
路徑:$ORACLE_HOME/rdbms/demo/mddemo.sql
涵蓋:TABLE、INDEX、CONSTRAINT、TRIGGER、GRANT、AUDIT、COMMENT、MATERIALIZED VIEW、TYPE、SYNONYM、SEQUENCE 等完整 DDL 提取範例。
| 參數 | 值 | 效果 |
|---|---|---|
| PRETTY | TRUE/FALSE | 格式化縮排、換行 |
| SQLTERMINATOR | TRUE/FALSE | 結尾加分號 |
| SEGMENT_ATTRIBUTES | TRUE/FALSE | 包含 TABLESPACE、STORAGE、PCTFREE 等 |
| STORAGE | TRUE/FALSE | 包含 STORAGE 子句 |
| CONSTRAINTS | TRUE/FALSE | 包含約束條件 |
| REF_CONSTRAINTS | TRUE/FALSE | 包含外鍵 |
| FORCE | TRUE/FALSE | 強制建立 (IF NOT EXISTS) |
| INHERIT | TRUE/FALSE | 繼承父表屬性 (Partition) |
expdp + DBMS_METADATA 完整備份物件定義SELECT_CATALOG_ROLE 或 DBA 權限查詢其他 SchemaSET LONG、SET LONGCHUNKSIZE 避免截斷