DDL Generation--Oracle's Answer to Save You Time and Money

Thursday May 1st 2003 by James Koopmann
Share:

The days of building your own DDL extraction utilities are almost gone; Oracle has given us a simple way to generate the DDL for objects defined in the database. Join James Koopmann as he delves into DBMS_METADATA.GET_DDL.

Oracle has once again given us what we asked for, almost. A simple way to generate the DDL for objects defined in the database.

The Old Method

It used to be that in order to generate object DDL from the Oracle database, we had one of three methods we could use.

Build the SQL

Most of us used this method. We would have to first search through the available DBA_ views, piece together a wiz bang SQL statement, determine the proper syntax for the object we were reverse engineering, and pray that Oracle did not change syntax or put new features on our object in a future release.

Export Utility

The export utility was used with the show=y option and would just spit out the DDL. The problem with this was that we also had a bit of editing to do in order to put the fragmented pieces together. While this was a bit heavy on the editing, it did give us the satisfaction of knowing that the DDL was complete and we didn't need to worry about messing up syntax. Probably the greatest advantage to using the export utility was the ability to export a table's DDL along with all of its supporting DDL such as grants and constraints. Not only did you get all the DDL, you got a glimpse of the order to apply this DDL to not disrupt the natural order of applying constraints if you needed to rebuild the object.

Buy Third Party Software

If you had the money, you could purchase a product that knew everything about how Oracle pieced together DDL from the internal views. This was the easiest but if you did not have a budget, you would need to revert to building your own.

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')

Supported Object Types

Here is a glimpse of the supported object types that can be input to the two previous function calls. Look at the Oracle documentation to determine the granularity you can achieve.

ASSOCIATION

associate statistics

AUDIT

audits of SQL statements

AUDIT_OBJ

audits of schema objects

CLUSTER

clusters

COMMENT

comments

CONSTRAINT

constraints

CONTEXT

application contexts

DB_LINK

database links

DEFAULT_ROLE

default roles

DIMENSION

dimensions

DIRECTORY

directories

FUNCTION

stored functions

INDEX

indexes

INDEXTYPE

indextypes

JAVA_SOURCE

Java sources

LIBRARY

external procedure libraries

MATERIALIZED_VIEW

materialized views

MATERIALIZED_VIEW_LOG

materialized view logs

OBJECT_GRANT

object grants

OPERATOR

operators

OUTLINE

stored outlines

PACKAGE

stored packages

PACKAGE_SPEC

package specifications

PACKAGE_BODY

package bodies

PROCEDURE

stored procedures

PROFILE

profiles

PROXY

proxy authentications

REF_CONSTRAINT

referential constraint

ROLE

roles

ROLE_GRANT

role grants

ROLLBACK_SEGMENT

rollback segments

SEQUENCE

sequences

SYNONYM

synonyms

SYSTEM_GRANT

system privilege grants

TABLE

tables

TABLESPACE

tablespaces

TABLESPACE_QUOTA

tablespace quotas

TRIGGER

triggers

TRUSTED_DB_LINK

trusted links

TYPE

user-defined types

TYPE_SPEC

type specifications

TYPE_BODY

type bodies

USER

users

VIEW

views

XMLSCHEMA

XML schema

Where To Go From Here

The direction that Oracle has gone is a good one. I really love these function calls. You should research and see where it fits into your organization. The one item I do think is missing is the ability to generate a full DDL map for an object. I personally would like to execute just one function and be able to get not only the table DDL, but also all the constraints, indexes, grants, and so on associated with the table without having to execute for each and every object type that might make up the table in question. When Oracle does this, we will truly have a powerful solution to DDL extraction and be able to, with precision and confidence, do maintenance on any part of an object within Oracle.

» See All Articles by Columnist James Koopmann

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved