Re: Using dbms_metadata to extract a full schema

Hi,

I found some problems using the DBMS_METADATA package
and procedures, especially with large DDL statements.
We were using v9.2.0.1 DB, UTF-8 and mixed bag of
column types, e.g. some tables with column_value01
VARCHAR2( 4000 CHAR ) and others with column_value02
VARCHAR2( 4000 BYTE ) (from memory so syntax errors
are my own). I am attempting to highlight the
meaninglessness (word?) of VARCHAR2( 4000 ) in UTF-8. 

Following the examples/docs/experience/whatever, its
quite complicated to get every schema object and type,
even just a complete list of objects. I mean it
usually takes about four or more procedure calls just
to setup the correct 'environment' to format your
extraction.

What I finally did was generate two passes. First was
a complete list of the commands to do the extraction
which at least included one command for every object
(this list was good to check when objects were added
or dropped). Second pass was to attempt to run the
commands in a 'reasonable' time frame while capturing
the spooled output. Good luck, you can waste some time
if you attempt to reverse a large schema like Siebel.
:-(

I hated when there were extra 'random' CR-LF
characters inserted in large table creation DDL. I
attempted to 'very clever' and parse the DDL so I
could successfully format the result with a tool like
PLFormatter. Looking back I think it was more trouble
than it was worth, but I was 'very clever' and wasted
more than two days debugging that junk. Extracting
packages, procedures and the like (triggers, etc) are
a complete waste of time with DBMS_METADATA (triggers
had bugs).

In summary, others have posted suggestions here about
tools that can reverse entire schema's DDL. I would
try those first. :-)

But, I'm sure complete Oracle education classes could
be taught around DBMS_METADATA... (okay its Friday and
I'm drifting...).

Regards,

Mike Thomas

PS: My work was on some early versions of 9.2 and its
remotely possible Oracle has fixed all the problems
I've reported above. In that case, disregard. ;-)

> call. Of course, this would make sense and the
> documentation
> SAYS you can do it, but it looks not to be the case.
> 
> Regards,
> Daniel
> 


        
                
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs  
http://hotjobs.sweepstakes.yahoo.com/careermakeover 
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: