| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Apr 30 2004 - 17:18:02 CDT
 |
 |