The New Method

The days of building your own DDL extraction utilities are almost gone. While Oracle has given us a simplistic approach to look at the DDL, it isn't quite everything we would hope for.

DBMS_METADATA.GET_DDL

This is the new package and function that will produce the DDL for you. In its simplest form, all you need to do is provide an object_type and an object_name.

The following example will generate the DLL for the DBA_TABLES view.

set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL('VIEW','DBA_TABLES') FROM dual;

If you want to get fancy, you can generate all of the views for a particular user by just joining the function to the dba_objects view. Here is an example that will generate all the DDL for all views owned by the user 'SYS'.

set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)
FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'VIEW' AND OWNER = 'SYS';

As you can see, this new functionality is very powerful.

Suppose you want to generate all 'CREATE USER' statements for all the users in your system, just issue the following:

SELECT DBMS_METADATA.GET_DDL('USER', username) FROM DBA_users;

Or maybe you want all the 'CREATE TABLESPACE' statements, try this.

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', tablespace_name) 
  FROM DBA_tablespaces;

The power is almost endless.

DBMS_METADATA.GET_DEPENDENT_DDL

The final piece of the pie to generate all the DDL for a given object is the use of the GET_DEPENDENT_DDL function. This is handy for extracting DDL that is in addition to the normal object definition. These are items such as grants and referential integrity.

Suppose you want to generate all the DDL for constraints that affect a table, you could use the following SQL. Please notice that while the first half of this SQL will produce output where the table in question is the child in the relationship. The second part of the SQL query will generate DDL for the tables that reference the table in question as the parent. This means that there might be other relationships produced by the second part of this query that do not affect the table in question.

SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
  FROM (SELECT DISTINCT b.table_name, b.owner
          FROM dba_constraints a,
               dba_constraints b
         WHERE b.r_constraint_name = a.constraint_name
           AND b.r_owner           = a.owner
           AND a.constraint_type in ('P','U')
           AND b.constraint_type = 'R'
           AND b.owner = '<owner>'
           AND b.table_name = 'table_name')
union
SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
  FROM (SELECT DISTINCT a.table_name, a.owner
          FROM dba_constraints a,
               dba_constraints b
         WHERE a.r_constraint_name = b.constraint_name
           AND a.r_owner           = b.owner
           AND b.constraint_type in ('P','U')
           AND a.constraint_type = 'R'
           AND b.owner = 'owner'
           AND b.table_name = 'table_name')

Previous   Next

Prev  [1] [2] [3] Next