This is something useful, especially for moving data between systems with very different storage configuration, or if you just want to have smaller output for your DDL command. Below you’ll find some examples that I was able to find:
SYS@TEST> create table test (id number, ddate date); Table created. Elapsed: 00:00:00.02 SYS@TEST> select dbms_metadata.get_ddl('TABLE','TEST') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE','TEST') -------------------------------------------------------------------------------- CREATE TABLE "SYS"."TEST" ( "ID" NUMBER, "DDATE" DATE ) 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM" Elapsed: 00:00:00.04
Now let’s changes some params:
SYS@TEST> begin dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false); dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false); dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false); dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false); dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false); dbms_metadata.set_transform_param (dbms_metadata.session_transform,'PRETTY',true); end; / PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 SYS@TEST> select dbms_metadata.get_ddl('TABLE','TEST') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE','TEST') -------------------------------------------------------------------------------- CREATE TABLE "SYS"."TEST" ( "ID" NUMBER, "DDATE" DATE ) Elapsed: 00:00:00.19 SYS@TEST>
Now, let’s put the params to their DEFAULT values:
SYS@TEST> begin dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'DEFAULT'); end; / PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 SYS@TEST> set long 10000 SYS@TEST> select dbms_metadata.get_ddl('TABLE','TEST') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE','TEST') -------------------------------------------------------------------------------- CREATE TABLE "SYS"."TEST" ( "ID" NUMBER, "DDATE" DATE ) 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM" Elapsed: 00:00:00.03