DBMS_METADATA.OPEN('DATABASE_EXPORT') leads to ORA-01427
Date: Tue, 16 Mar 2010 21:43:39 -0400
The task is to recreate the database' DDL. This particular database is a legacy, wished upon us by the previous contract incumbent. It appears to have grown like Topsy and we are working without benefit of documentation. Recreation of the DDL will, at least, endow us with a set of scripts that we can put under version control.
As the subject line indicates, I had thought to exploit features of the DBMS_METADATA package to make one quick and easy swoop through the database.
I understand that these features of DBMS_METADATA are closely related to and descended from data pump.
As it happens, I do not have the constructed script here in front of me for review (pesky security restrictions, don't you know). In brief:
h NUMBER; -- handle th NUMBER; -- handle for the transform doc CLOB; -- clob to hold the extracted ddlbegin
h := DBMS_METADATA.OPEN('DATABASE_EXPORT'); th := DBMS_METADATA.GET_TRANSFORM_PARAM(h,'DDL');
doc := DBMS_METADATA.FETCH_CLOB(h);
EXIT WHEN doc IS NULL;
INSERT INTO my_ddl VALUES doc:
It compiles with errors ... and SHOW ERRORS reveals the ORA-01427!
*ORA-01427*: single-row subquery returns more than one row
Metalink, excuse me, MOS, offers no clues
I have been thinking that there is more than one CLOB in "doc" somehow.
I had also thought maybe I was be overly simplistic, that perhaps I
need to loop
through the individual object types. But where is the document that enumerates these
object types and defines the order in which they should be extracted?
Then I found Andre van Winssen's post in this forum from January, discussing Bug 9214753 ...
this is Bug 9214753: EXPDP RETURNS ORA-1427 WHEN SAME PARTITIONED IOT EXISTS
IN EXPORTING SCHEMA
solution is to add following line in subselect in select list of view
(created in catmeta.sql):
create or replace force view ku$_iotpart_data_view of ku$_table_data_t with object OID(obj_num)
as select '1','2',
from obj$ o1
and o1.owner#=o.owner_num -- <<<< fix for bug 9214753 )
I think these two cases may be distantly related, but how do I proceed from here?
Any insights are welcome