ORA-31603 on dbms_metadata.get_dll for dblinks only, get_dll returns ORA-31603 for dblink but not for other db objects Options

[elutsky]

Hi,

I have user U1 with dblink DBL1 (private dblink, not public).

CODE

CREATE DATABASE LINK DBL1
CONNECT TO U1 IDENTIFIED BY XX USING 'TNS1';

I have user U2 with dblink DBL2 (private dblink, not public).

CODE

CREATE DATABASE LINK DBL2
CONNECT TO U2 IDENTIFIED BY XX USING 'TNS2';

Both dblinks works fine, it means I can do select.

When I logged in with U1 and try to execute the following statement :

CODE

SELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME)
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME   = 'DBL1';

I get ORA-31603 error:

CODE

ORA-31603: object "DBL1" of type DB_LINK not found in schema "U1"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1

same happens when I pass user name to get_ddl:

CODE

SELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME, [b]USER[/b])
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME   = 'DBL1';

When I select from user_objects with the following query , it is there.

CODE

SELECT *
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME   = 'DBL1';

DBMS_METADATA.GET_DDL works for every other objects of U1 (tables, views, ... for example) except for dblinks.

DBMS_METADATA.GET_DDL works for every objects of U2, includes DBL2.
What can cause this behavior?

I tried to add "select catalog role" to U1, even when I know that I need it only for objects from other users.
I tried to create DBL2 in U1 - same results.
I tried to re-create dblink using full connection string - with same result.

[burleson]

Hi,

I see that nobody on OTN would help you . . . .

I would open an SR on MOSC, this looks like a bug:

http://support.oracle.com

ORA-31603

See here:

http://www.dba-oracle.com/sf_ora_31603_obj..._string_bc1.htm

Many Oracle users find themselves encountering ORA-31603 when using DBMS_METADATA.

Oracle MOSC has documentation regarding ORA-31603 which applies to PL/SQL - Version: 9.2.0.6; occurring on any platform. The symptoms of this particular problem are defined as:

using DBMS_METADATA package against tables defined with partitions to generate DDL when the column contains unused columns raises ORA-31603.

The reason ORA-31603 is encountered in cases such as these is because, "unused columns defined on the table cause Metadata retrieval issue.