09 October 2009

Just the plain DDL, please.. DBMS_METADATA

The other day I needed to get some DDL statements from the datadictionary. In the old days you could write your own queries to extract all this. Nowadays you can use the built in package DBMS_METADATA to get the DDL for you.
Let's take a look at how you can use this. The DDL that I want to extract is the infamous EMP table, normally in the SCOTT schema.
SQL> create table emp
  2  as
  3  select *
  4    from scott.emp
  5  /

Table created.

To make it a little more interesting, we also add the DEPT table and place some constraints on them.
SQL> create table dept
  2  as
  3  select *
  4    from scott.dept
  5  /

Table created.

SQL> 
SQL> alter table dept
  2  add constraint dept_pk primary key (deptno)
  3  /

Table altered.

SQL> 
SQL> alter table emp
  2  add constraint emp_dept_fk foreign key (deptno) references dept (deptno)
  3  /

Table altered.

If you use DBMS_METADATA just like that, you might get more than you asked for.
SQL> set long 5000
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP')
  2    from dual
  3  /

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "ALEX"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPTNO")
          REFERENCES "ALEX"."DEPT" ("DEPTNO") ENABLE
   ) 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)
  TABLESPACE "USERS"


As you can in the code block above, you get the complete DDL including the storage clauses and the constraints. If this is not what you want, and I didn't want all this, than you need to modify some transformation parameters.

SQL> 
SQL> begin
  2     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
  3     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
  4     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
  5     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
  6     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Each of these transformation parameters take out bit by bit parts of the generated DDL. After running the above anonymous block we will get the following result.
SQL> select dbms_metadata.get_ddl ('TABLE', 'EMP')
  2    from dual
  3  /

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "ALEX"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   )


That's more like it, just what I was after. The plain DDL for the EMP table.
One of the nice things is that you don't need to modify all the transformation parameters again to go back to the default. They made it really easy to return to the default settings:
SQL> begin
  2     dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'DEFAULT');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> 


Documentation on DBMS_METADATA

UPDATE July 10, 2013 (especially for Erhan Sarigul): Remove the Schema from the DDL-script

create or replace function remap_schema return clob is
   -- Define local variables.
   h   number; --handle returned by OPEN
   th  number; -- handle returned by ADD_TRANSFORM
   doc clob;
begin
   -- Specify the object type.
   h := dbms_metadata.open('TABLE');
   -- Use filters to specify the particular object desired.
   dbms_metadata.set_filter(h
                           ,'SCHEMA'
                           ,'ALEX');
   dbms_metadata.set_filter(h
                           ,'NAME'
                           ,'EMP');
   -- Request that the schema name be modified.
   th := dbms_metadata.add_transform(h
                                    ,'MODIFY');
   dbms_metadata.set_remap_param(th
                                ,'REMAP_SCHEMA'
                                ,'ALEX'
                                ,null);
   -- Request that the metadata be transformed into creation DDL.
   th := dbms_metadata.add_transform(h
                                    ,'DDL');
   -- Specify that segment attributes are not to be returned.
   dbms_metadata.set_transform_param(th
                                    ,'SEGMENT_ATTRIBUTES'
                                    ,false);
   -- Fetch the object.
   doc := dbms_metadata.fetch_clob(h);
   -- Release resources.
   dbms_metadata.close(h);
   return doc;
end remap_schema;
/

select remap_schema -- dbms_metadata.get_ddl ('TABLE','EMP')
  from dual
/

Using the DBMS_METADATA API

19 comments:

  1. Thank you for sharing the code! I will certainly try it and hope that it will work great for me as well..

    ReplyDelete
  2. Any way to discard the partitions also?

    ReplyDelete
    Replies
    1. Yes, there is...
      begin
      dbms_metadata.set_transform_param(dbms_metadata.session_transform
      ,'PARTITIONING'
      ,false);
      end;
      /

      Delete
  3. How to remove the schema name from the output script?

    ReplyDelete
  4. CREATE TABLE "ALEX"."EMP"
    ( "EMPNO" NUMBER(4,0),
    "ENAME" VARCHAR2(10),
    "JOB" VARCHAR2(9),
    "MGR" NUMBER(4,0),
    "HIREDATE" DATE,
    "SAL" NUMBER(7,2),
    "COMM" NUMBER(7,2),
    "DEPTNO" NUMBER(2,0)
    )

    How we can move the "ALEX" from above script?

    ReplyDelete
    Replies
    1. This involves a little more steps: In the documentation there is an example which remaps the schema to a different one, but you can also pass a NULL. I will add the complete function to the blogpost, so you don't have to go through the docs to find it.

      Delete
    2. Thanks Alex, I will be waiting for your completed function.

      Delete
    3. The minute I posted my reply, I added the function to the blogpost (at the bottom)...

      Delete
    4. I don't see it at the bottom? can you send the function again?

      Delete
    5. I also need this function...?Where is it.

      Delete
  5. I dont see blogpost at the bottom? can you send me the link or can you post the function here?

    ReplyDelete
  6. Alex you are the best :)) Thanks

    ReplyDelete
  7. Hi alex,

    I used REGEXP_REPLACE to delete Scott. is there any way to delete Scott and Storage information same time using REGEXP_REPLACE...?

    select REGEXP_REPLACE(dbms_metadata.get_ddl('TABLE','EMP'),'("Scott".)','', 1, 0, 'i') from dual;

    result:

    CREATE TABLE "EMP"
    ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
    "ENAME" VARCHAR2(10),
    "JOB" VARCHAR2(9),
    "MGR" NUMBER(4,0),
    "SAL" NUMBER(7,2),
    "DEPTNO" NUMBER(2,0)
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    TABLESPACE "USERS"

    ReplyDelete
  8. @eric: not sure why you want to use REGEXP_REPLACE to do what DBMS_METADATA gives you out of the box? Using the settings as described in this blogpost will give you exactly what you need.

    ReplyDelete
  9. Any way of scripting out a FGA or RLS policy on a table as well?

    ReplyDelete
    Replies
    1. Yes, it's possible to get these using DBMS_METADATA; they are in the list of supported Object Types: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm

      Delete