Monthly Archives: June 2016

Standard

Posted by

Posted on

June 22, 2016

Posted under

Import&Export, Oracle

Comments

Leave a comment

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