Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> ORA-31603 on dbms_metadata.get_dll for dblinks only, get_dll returns ORA-31603 for dblink but not for other db objects
elutsky
post Feb 22 2012, 02:48 AM
Post #1


Member
**

Group: Members
Posts: 12
Joined: 22-July 10
Member No.: 43,527



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.
Go to the top of the page
 
+Quote Post
burleson
post Feb 22 2012, 06:02 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,331
Joined: 26-January 04
Member No.: 13



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.





--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 30th July 2014 - 06:05 AM