DBMS_METADATA.OPEN('DATABASE_EXPORT') leads to ORA-01427

From: Gus Spier <gus.spier@xxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 16 Mar 2010 21:43:39 -0400
Hi, List!

Solaris 10
Oracle 10.2.0.3

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:

declare
h NUMBER; -- handle
th NUMBER; -- handle for the transform
doc CLOB; -- clob to hold the extracted ddl
begin
h := DBMS_METADATA.OPEN('DATABASE_EXPORT');
th := DBMS_METADATA.GET_TRANSFORM_PARAM(h,'DDL');

...

LOOP
   doc := DBMS_METADATA.FETCH_CLOB(h);
   EXIT WHEN doc IS NULL;
   INSERT INTO my_ddl VALUES doc:
END LOOP;
DBMS_METADATA.CLOSE(h);
END;
/

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
ku$_iotpart_data_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',
ip.obj#,
(select o1.obj#
from obj$ o1
where o1.name=bo.name
and o1.subname=o.subname
and o1.owner#=o.owner_num    -- <<<< fix for bug 9214753
  )
,o.subname,
NULL,
...

I think these two cases may be distantly related, but how do I proceed
from here?

Any insights are welcome

Regards,
Gus
DBMS_METADATA.OPEN('DATABASE_EXPORT') leads to ORA-01427

Other related posts:
