export-ddl-with-dql

 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>