Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle dbms_metadata
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

DBMS_METADATA

One of the most sacred job functions many DBAs perform is that of the “guardian of the database design”, most often manifested as simply meaning the “keeper of the DDL”. Even though there are many database change-management tools and data-modeling tools with model-to-database alteration/synchronization capabilities, numerous DBAs still generally rely on DDL scripts. Some DBAs might have those DDL scripts in a source-code version-control system, but even that is not the norm. So these “keepers of the DDL” have always had a staggering task in being able to produce current scripts for whatever objects the database might contain.

 

This challenge is only magnified by the sheer number of people, and in some cases, even applications tasked with creating database objects. In many cases, this database design sprawl is so bad that the database itself has become the central repository of the design through sheer necessity as the Oracle data dictionary may be the only guaranteed, accurate and up-to-date copy of the database design. Obviously, that is not a good place to be in, but many find themselves there nonetheless. So reverse engineering DDL from the data dictionary is quite often a necessary evil. Some people refer to reverse engineering as database, schema or object definition or DDL extraction.

 

In the early days when the Oracle database was much less robust and therefore much simpler, DBAs often wrote SQL scripts to generate the database objects’ DDL code. These scripts simply queried that data dictionary and produced human readable SQL files of what the database design reality was at that particular time. The old_generate_table_ddl.sql SQL*Plus script, shown below, is a simple example of what such a script might have looked like.

 

   old_generate_table_dll.sql script

 

 

set echo off

set heading off

set feedback off

set verify off

set pagesize 0

set linesize 132

 

define schema=&1

define CR=chr(10)

define TAB=chr(9)

col x noprint

col y noprint

select  table_name y,

        0 x,

        'CREATE TABLE ' ||

        rtrim(table_name) ||

        '('

from    dba_tables

where   owner = upper('&schema')

union

select  tc.table_name y,

        column_id x,

        decode(column_id,1,'    ','   ,')||

        rtrim(column_name)|| &TAB || &TAB ||

        rtrim(data_type) ||

        rtrim(decode(data_type,'DATE',null,'LONG',null,

               'NUMBER',decode(to_char(data_precision),null,null,'('),

               '(')) ||

        rtrim(decode(data_type,

               'DATE',null,

               'CHAR',data_length,

               'VARCHAR2',data_length,

               'NUMBER',decode(to_char(data_precision),null,null,

                 to_char(data_precision) || ',' || to_char(data_scale)),

               'LONG',null,

               '******ERROR')) ||

        rtrim(decode(data_type,'DATE',null,'LONG',null,

               'NUMBER',decode(to_char(data_precision),null,null,')'),

               ')')) || &TAB || &TAB ||

        rtrim(decode(nullable,'N','NOT NULL',null))

from    dba_tab_columns tc,

        dba_objects o

where   o.owner = tc.owner

and     o.object_name = tc.table_name

and     o.object_type = 'TABLE'

and     o.owner = upper('&schema')

union

select  table_name y,

        999999 x,

        ')'  || &CR

        ||'  STORAGE('                                   || &CR

        ||'  INITIAL '    || initial_extent              || &CR

        ||'  NEXT '       || next_extent                 || &CR

        ||'  MINEXTENTS ' || min_extents                 || &CR

        ||'  MAXEXTENTS ' || max_extents                 || &CR

        ||'  PCTINCREASE '|| pct_increase                || ')' ||&CR

        ||'  INITRANS '   || ini_trans                   || &CR

        ||'  MAXTRANS '   || max_trans                   || &CR

        ||'  PCTFREE '    || pct_free                    || &CR

        ||'  PCTUSED '    || pct_used                    || &CR

        ||'  PARALLEL (DEGREE ' || rtrim(DEGREE) || ') ' || &CR

        ||'  TABLESPACE ' || rtrim(tablespace_name)      ||&CR

        ||'/'||&CR||&CR

from    dba_tables

where   owner = upper('&schema')

order by 1,2;

 

When the old_generate_table_ddl.sql SQL*Plus script is run against the MOVIES demo schema, here is a sample of what the generated DDL looks like for one of the tables.

 

old_generate_table_ddl.sql output

 

 

CREATE TABLE CUSTOMER(

    CUSTOMERID          NUMBER(10,0)            NOT NULL

   ,FIRSTNAME           VARCHAR2(20)            NOT NULL

   ,LASTNAME            VARCHAR2(30)            NOT NULL

   ,PHONE               CHAR(10)                NOT NULL

   ,ADDRESS             VARCHAR2(40)            NOT NULL

   ,CITY                VARCHAR2(30)            NOT NULL

   ,STATE               CHAR(2)                    NOT NULL

   ,ZIP               CHAR(5)                  NOT NULL

   ,BIRTHDATE           DATE

   ,GENDER              CHAR(1)

)

  STORAGE(

  INITIAL 1048576

  NEXT 1048576

  MINEXTENTS 1

  MAXEXTENTS 2147483645

  PCTINCREASE 0)

  INITRANS 1

  MAXTRANS 255

  PCTFREE 10

  PCTUSED

  PARALLEL (DEGREE 1)

  TABLESPACE USERS

/

 

This is not too bad. But with the plethora of table structural design options such as clustering, partitioning, index organized tables, external tables and such, it is clear that this little script would need thousands of lines of code plus more of the same for indexes and views. Also, keep in mind all the fun database objects such as materialized views, materialized view logs, queue tables, and sequences, to name a few, not to mention the entire security model for the whole enchilada such as roles and grants. It is pretty clear that DDL generation scripts such as these have met their match.

 

So what is a body to do? Thankfully, Oracle came to the rescue with a package to implement database object reverse engineering, namely dbms_metadata. Not only that, but both SQL Developer and OEM make use of it. Therefore, for those who just need a quick and easy way to peruse their databases DDL, Oracle’s free SQL Developer tool is probably good enough, as shown in the following screen snapshot. But for those who want to delve even further into the mysteries of everything the dbms_metadata package can do, read on.

 

Figure 6.4:  Oracle SQL Developer

 

The good news is that Oracle now provides a very robust mechanism for extracting or reverse engineering the database objects’ DDL. But like anything else, there is also some bad news and here are the couple items worth note:

  • Does not generate the DDL in required object dependency order for referential integrity constraints

  • Is not very well documented (the Oracle Utilities manual describes the syntax and some basic cases – but not nearly enough)

  • Slightly over-engineered – such references can be found on various blogs and presentations posted to the web

  • IS NOT SUPPORTED FOR v7-8-9iR1 databases

  • Seems to be buggy from what can be told on OTN

Please do not let the API’s complexity and lack of robust documentation be a swaying factor – dbms_metadata is a must-have and use utility. An entire chapter could be written on just this one topic, but instead of trying to show the complete syntax reference and detailing all aspects, three very useful use case scenarios will be presented. These three examples will be used as is or serve as a good foundation upon which to add one’s own modifications. And while there are just three examples, it should be noted that there are really just two ways to work with dbms_metadata: going for one object type at a time, or going for collections of object types simultaneously.


For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

    

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.