Setting the output of get_ddl with dbms_metadata.set_transform_param package

DDL metadata command skip the Tablespace and storage clause from the output

Following is used for before DBMS_METADATA.GET_DDL for setting the output format for ddls commands:

— SQL Terminator is placed “;” on every SQL statement if you spool multiple DDL in one spool file.
EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

–Tablespace clause is disabled from DDL command output.
EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);

–Storage clause is disable from DDL command output
EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);

–Segment attributes is disable from DDL command output.
EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);

–Output of metadata in particular patterns for easy to read.
EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);

Following are the example of using the upper commands:
Use following statement to show the example of different format. Following all statement is executed in one session.

GET_DDL return all information when we fetch DDL from DBMS_METADATA
set heading off;
set echo off;
Set pages 999;
set long 10000;
select DBMS_METADATA.GET_DDL('TABLE','TEST','HR') from dual;

select DBMS_METADATA.GET_DDL('TABLE','TEST','HR') from dual;
  CREATE TABLE "HR"."TEST"
   (    "ID" NUMBER,
        "CUST_ID" NUMBER,
        "ITEM_ID" NUMBER,
        "QUANTITY" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  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 "SYSAUX"

Remove tablespace clauses from output of metadata command
EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);

select DBMS_METADATA.GET_DDL('TABLE','TEST','HR') from dual;
   CREATE TABLE "HR"."TEST"
   (    "ID" NUMBER,
        "CUST_ID" NUMBER,
        "ITEM_ID" NUMBER,
        "QUANTITY" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  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)


Remove Storage Clause from output of Metadata Commands
EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);
EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);

select DBMS_METADATA.GET_DDL('TABLE','TEST','HR') from dual;
  CREATE TABLE "HR"."TEST"
   (    "ID" NUMBER,
        "CUST_ID" NUMBER,
        "ITEM_ID" NUMBER,
        "QUANTITY" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING


Remove the segment attribute from DDL output
EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);
EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);

SQL> select DBMS_METADATA.GET_DDL('TABLE','TEST','HR') from dual;
  CREATE TABLE "HR"."TEST"
   (    "ID" NUMBER,
        "CUST_ID" NUMBER,
        "ITEM_ID" NUMBER,
        "QUANTITY" NUMBER
   )


Place the terminator on every SQL query on DDL metadata output
It will placed terminator at end of every query if you are using DDL Metadata package for multiple DDL in one spool file.
EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

  CREATE TABLE "HR"."TEST"
   (    "ID" NUMBER,
        "CUST_ID" NUMBER,
        "ITEM_ID" NUMBER,
        "QUANTITY" NUMBER
   ) ;

Leave a Reply

Fill in your details below or click an icon to log in:

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.