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:

[email protected]> create table test (id number, ddate date);

Table created.

Elapsed: 00:00:00.02


[email protected]> 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:

[email protected]> 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


[email protected]> 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
[email protected]>

Now, let’s put the params to their DEFAULT values:

[email protected]> begin
 dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'DEFAULT');
end;
/ 

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02


[email protected]> set long 10000
[email protected]> 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