How to get DDL for DB objects and Dependent Objects

It was very tough to get the DDL for database objects in eralier versions of Oracle. But from Oracle 9i, it is pretty easy to get the DDL of Database Objects.

In Oracle9i we have the exciting new dbms_metadata utility to display DDL directly from the data dictionary. Using this powerful utility, we can punch individual objects or an entire schema.

Best of all, it is easy. You simply execute dbms_metadata. get_ddl.

To punch off all table and indexes for the EMP table, we execute dbms_metadata. get_ddl, select from DUAL, and providing all required parameters.


set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

spool off;

Here is the output. The only thing missing is the ending semicolons after each statement. Just for illustration, we show how a primary key can be punched as part of the table DDL or separately using the INDEX argument.

CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"


CREATE UNIQUE INDEX "SCOTT"."DEPT_IDX" ON "SCOTT"."DEPT" ("DNAME")
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"

Now we can modify the syntax to punch a whole schema. It us easily done by selecting dbms_metadata. get_ddl and specifying USER_TABLES and USER_INDEXES. :

set pagesize 0
set long 90000
set feedback off
set echo off

spool scott_schema.sql

connect scott/tiger;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;

spool off;

If the specified objectname is not found, this packaged procedure throws an error.

e.g.

SQL> SELECT dbms_metadata.get_dependent_ddl('INDEX','T1','SCOTT') FROM dual;
ERROR:
ORA-31608: specified object of type INDEX not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 2808
ORA-06512: at "SYS.DBMS_METADATA", line 4394
ORA-06512: at line 1


Note: Above all we need to use 'select' query to get the result rather 'execute'
all the parameters passing to the packged procedure are case sensitive in general in upper case
Note: Make sure you give proper sql*plus session settings like
set wrap on
column column_name a5000
set trimspool on
set echo off
set long 6000

The below are the object types that are supported.
OBJECT TYPES
----------------
ASSOCIATION
AUDIT
AUDIT_OBJ
CLUSTER
COMMENT
CONSTRAINT
CONTEXT
DB_LINK
DEFAULT_ROLE
DIMENSION
DIRECTORY
FUNCTION
INDEX
INDEXTYPE
JAVA_SOURCE
LIBRARY
MATERIALIZED_VIEW
MATERIALIZED_VIEW_LOG
OBJECT_GRANT
OPERATOR
OUTLINE
PACKAGE
PACKAGE_SPEC
PACKAGE_BODY
PROCEDURE
PROFILE
PROXY
REF_CONSTRAINT
ROLE
ROLE_GRANT
ROLLBACK_SEGMENT
SEQUENCE
SYNONYM
SYSTEM_GRANT
TABLE
TABLESPACE
TABLESPACE_QUOTA
TRIGGER
TRUSTED_DB_LINK
TYPE
TYPE_SPEC
TYPE_BODY
USER
VIEW


1. An Object types you can see from querying the DBA_OBJECTS view for object_type.
2. A predefined type supplied by Oracle for use in the DBMS_METADATA functions such as REF_CONSTRAINT, OBJECT_GRANT, etc.




DBMS_METADATA.GET_DEPENDENT_DDL:
Often times we need to determine how a particular object relates to other objects in our database. This is what the GET_DEPENDENT_DDL function was created for. With this function it is easy to determine such things as the grants on an object, the referential integrity between objects, and the indexes on an object. This function can be a bit confusing when supplying inputs to the function call. What is needed to remember is that your are asking for items that are dependent on a named object in Oracle. Here are a few examples to help you get familiar with the proper use of this powerful function.

Suppose you wanted to extract all source code for triggers where a particular table was used in? You no longer need to search through DBA_SOURCE you need only issue the following SQL. Please note that it is the object_type that is dependent on the named object . Extend this query a bit and you can see how you might be able to extract all source code for an object with a simple changing of TRIGGER to include PROCEDURE, PACKAGE, FUNCTION, and JAVA_SOURCE.

e.g.

SQL> SELECT dbms_metadata.get_dependent_ddl('INDEX','T','SCOTT') FROM dual;

CREATE INDEX "SCOTT"."I_2" ON "SCOTT"."T" ("LNAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

CREATE INDEX "SCOTT"."T_I" ON "SCOTT"."T" ("FNAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"



'CONSTRAINT' and 'REF_CONSTRAINT' key words can be used only with get_dependent_ddl but not with get_ddl.

There are two constraints and one ref constraints defined on the table T4. If 'CONSTRAINT' is used then we see only the defination of two constraints but not the refconstraints. The same with 'REF_CONSTRAINT'.
Below are the examples.

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT','T4','SCOTT') from dua
l;

ALTER TABLE "SCOTT"."T4" ADD PRIMARY KEY ("A")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE

ALTER TABLE "SCOTT"."T4" ADD UNIQUE ("B")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE



Elapsed: 00:00:00.17
SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','T4','SCOTT') from
dual;

ALTER TABLE "SCOTT"."T4" ADD FOREIGN KEY ("C")
REFERENCES "SCOTT"."T6" ("A") ENABLE



Elapsed: 00:00:00.12
SQL> SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','T4','SCOTT') from dual;
ERROR:
ORA-31603: object "T4" of type REF_CONSTRAINT not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1



no rows selected

Elapsed: 00:00:00.12
SQL> SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','T4','SCOTT') from dual;
ERROR:
ORA-31603: object "T4" of type CONSTRAINT not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1



DBMS_METADATA.GET_GRANTED_DDL
The purpose of the GET_GRANTED_DDL function is to generate the DDL required around grants given to a particular user.

If you wanted to generate the system grants given to a user.
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','') from dual;

If you wanted to generate the roles granted to a user.
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','') from dual;

If you wanted all the object grants given to a user.
select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','') from dual;