↓ 快轉到主要內容

export-ddl-with-dql

·575 字·2 分鐘·
PolloChang
作者
PolloChang
我是一隻雞

以下方法可以產出出 Table, Index 等 DDL 的 SQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
SQL> SET LONG 20000
SQL> SET LONGCHUNKSIZE 20000
SQL> SET LINESIZE 200
SQL> SET PAGESIZE 0
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX', 'ORDERS_I2', 'TPCC') DDL_SQL FROM DUAL;

  CREATE UNIQUE INDEX "TPCC"."ORDERS_I2" ON "TPCC"."ORDERS" ("O_W_ID", "O_D_ID", "O_C_ID", "O_ID")
  PCTFREE 10 INITRANS 4 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_HAMMER"


1 row selected.

SQL>

以下是匯出更簡短的方式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX', 'ORDERS_I2', 'TPCC') DDL_SQL FROM DUAL;

  CREATE UNIQUE INDEX "TPCC"."ORDERS_I2" ON "TPCC"."ORDERS" ("O_W_ID", "O_D_ID", "O_C_ID", "O_ID")
  PCTFREE 10 INITRANS 4 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "TS_HAMMER"


1 row selected.

SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', false);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX', 'ORDERS_I2', 'TPCC') DDL_SQL FROM DUAL;

  CREATE UNIQUE INDEX "TPCC"."ORDERS_I2" ON "TPCC"."ORDERS" ("O_W_ID", "O_D_ID", "O_C_ID", "O_ID")
  PCTFREE 10 INITRANS 4 MAXTRANS 255 COMPUTE STATISTICS


1 row selected.

SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX', 'ORDERS_I2', 'TPCC') DDL_SQL FROM DUAL;

  CREATE UNIQUE INDEX "TPCC"."ORDERS_I2" ON "TPCC"."ORDERS" ("O_W_ID", "O_D_ID", "O_C_ID", "O_ID")



1 row selected.

SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX', 'ORDERS_I2', 'TPCC') DDL_SQL FROM DUAL;

  CREATE UNIQUE INDEX "TPCC"."ORDERS_I2" ON "TPCC"."ORDERS" ("O_W_ID", "O_D_ID", "O_C_ID", "O_ID")
  ;


1 row selected.

SQL>

常用參數說明

  • SEGMENT_ATTRIBUTES (false):直接關閉所有區段屬性,包含 PCTFREE、INITRANS、MAXTRANS、STORAGE 子句與 TABLESPACE。

  • STORAGE (false):僅關閉 STORAGE(...) 區塊。

  • TABLESPACE (false):僅隱藏 TABLESPACE "..." 設定。

  • SQLTERMINATOR (true):若希望在產出的 DDL 結尾自動加上分號 ;,可加入此設定。

  • SEQ

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SELECT 
    sequence_name,
    'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || ';' || CHR(10) ||
    CHR(10) ||
    'CREATE SEQUENCE ' || sequence_owner || '.' || sequence_name || CHR(10) ||
    '  START WITH ' || last_number || CHR(10) ||
    '  MAXVALUE ' || max_value || CHR(10) ||
    '  MINVALUE ' || min_value || CHR(10) ||
    '  ' || CASE cycle_flag WHEN 'Y' THEN 'CYCLE' ELSE 'NOCYCLE' END || CHR(10) ||
    '  ' || CASE WHEN cache_size = 0 THEN 'NOCACHE' ELSE 'CACHE ' || cache_size END || CHR(10) ||
    '  ' || CASE order_flag WHEN 'Y' THEN 'ORDER' ELSE 'NOORDER' END || ';' AS ddl_sql
FROM all_sequences
WHERE sequence_owner = '${OWNER}'
and sequence_name = '${OBJECT_NAME}'
;