Monday, October 24, 2005

Giving up on dbms_metadata

My group was assigned a project to create a script that would dump the DDL of a schema to source. We figured dbms_metadata was the way to go and started writing our script. The process was to dump a particular object type, and then run that DDL on an empty schema to make sure it ran.

First thing we hit was good old bug 3832291:

ORA-31603: object "SYS_C0059941" of type CONSTRAINT not found in schema "MYSCHEMA"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

OK. We figured out a way around that by generating constraints as ALTER statements. As we're cooking along again, blam, bug 3721907. Sure, we found a way around that one too.

The final nail in the coffin, was the performance with the whole thing. It took 30 minutes to dump a small schema of about 200 tables and a couple dozen packages. Checked metalink and found good old bug 3653586.

Looks like most of these bugs are fixed in 10g, but I'm not upgrading a bunch of instances just so dbms_metadata works. Sigh.

4 comments:

Noons said...

The sooner Oracle realizes that this forced upgrade path in order to get simple bugs fixed is deadly, the better. No other db maker does this.

Haven't they been listening yet? Not a SINGLE user is happy with this state of affairs!

Anonymous said...

I am truly wondering which people at Oracle are writing these things. How hard is it to write some half decent script and why did it take 20 years to get one in the first place.

OracleDoc said...

Well, you could use this.
http://www.ddlwizard.com/

rajeXsh said...

Oracle 9206 on Solaris takes about 20min for 500 tables (incl indexes and constraints). no packages though