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.
|
