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
