Archive for May 31st, 2005
DBMS_METADATA
One of the things I have been working on recently is a simple script to extract schema creation ddl from various dev schemas that we have around the place, whose documentation is.. ,well perhaps light is the politest word.
anyway that was the idea, first set some transforms to get rid of extraneous stuff (like the storage clauses), and to add in the sqlterminator to each statement, then loop through the various types of objects in the appropriate order liberally sprinkling calls to the DBMS_METADATA.GET_xxx routines.
The first hint of trouble came when I tried to recall the published interface to DBMS_METADATA and couldn’t, no worries just log onto my local 10g box
SQL>DESC DBMS_METADATA
ERROR:
ORA-03117: two-task save area overflow
oops, that really shouldn’t happen.
Next, logon to the 9i box that I was actually interested in – fortunately this worked and I could get the get_ddl, get_granted_ddl etc syntax.
The next issue, and one that I alluded to earlier is that when the get_granted_ddl query should return no rows, in fact it returns an ugly error.
ERROR:
ORA-31608: specified object of type SYSTEM_GRANT not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 631
ORA-06512: at "SYS.DBMS_METADATA", line 1339
ORA-06512: at line 1
This according to the Tar I raised will be fixed in a later release. Incidentally the bug that is being fixed has as a work around Don’t use dbms_metadata.get_granted_ddl to return system grants. I don’t know about you but that feels a bit like saying don’t use Create table … to create tables.
The current problem that I have is that GET_DEPENDENT_DDL is also erroring out, and in this case the problem isn’t that no objects are dependent upon this schema (I don’t know what the problem actually is yet).
All in all not very satisfactory.