Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted May 1, 2003

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

By James Koopmann

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


Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date