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

From: Gus Spier <gus.spier_at_gmail.com>
Date: Tue, 16 Mar 2010 21:43:39 -0400
Message-ID: <6d3967611003161843v49111c69i88914fd662d215ee_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 16 2010 - 20:43:39 CDT