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
Excelent post. Leker!
ReplyDeleteThank you for sharing the code! I will certainly try it and hope that it will work great for me as well..
ReplyDeleteAny way to discard the partitions also?
ReplyDeleteYes, there is...
Deletebegin
dbms_metadata.set_transform_param(dbms_metadata.session_transform
,'PARTITIONING'
,false);
end;
/
How to remove the schema name from the output script?
ReplyDeleteCREATE TABLE "ALEX"."EMP"
ReplyDelete( "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?
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.
DeleteThanks Alex, I will be waiting for your completed function.
DeleteThe minute I posted my reply, I added the function to the blogpost (at the bottom)...
DeleteI don't see it at the bottom? can you send the function again?
DeleteI also need this function...?Where is it.
Deletethanks Alex..
DeleteI dont see blogpost at the bottom? can you send me the link or can you post the function here?
ReplyDeleteThanks Alex!!! I just see it.
ReplyDeleteAlex you are the best :)) Thanks
ReplyDeleteHi alex,
ReplyDeleteI 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"
@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.
ReplyDeleteAny way of scripting out a FGA or RLS policy on a table as well?
ReplyDeleteYes, 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
Deletethanks, what about trigger remove alter enable
ReplyDelete