Extract DDL without some parameters

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
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.