ORA-31603 On Dbms_metadata.get_dll For Dblinks Only

Feb 22, 2012

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

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

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

CODECREATE 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 :

CODESELECT 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:

CODEORA-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:

CODESELECT 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.

CODESELECT *
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.

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.

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: DBMS_METADATA.GET_DDL Fails With ORA-31603 On Dblink

Feb 21, 2012

I have user U1 with dblink DBL1 (private dblink, not public).When I logged in with U1 and try to execute the following statement :

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:

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

DBL1 script is as follows:

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

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

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

DBMS_METADATA.GET_DDL works for every other objects (tables for example) in this schema except for dblinks.

View 10 Replies View Related

PL/SQL :: Unable To Run Spatial Operations Through DBLinks?

Aug 16, 2012

I am trying to run spatial operations through dblinks. See the example query below

select a.OGC_GEOMETRY.sdo_gtype from <table>@dblink a where sdo_nn(a.OGC_GEOMETRY,mdsys.sdo_geometry(2001,null,mdsys.sdo_point_type(0,0,null),null,null),'sdo_num_res=1')='TRUE'.

Query fails with the following error

ORA-13249:
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 17
ORA-06512: at "MDSYS.PRVT_IDX", line 9
*13249. 00000 - "%s"*
**Cause: An internal error was encountered in the extensible spatial index*
component. The text of the message is obtained from some
other server component.
**Action: Contact Oracle Support Services with the exact error text.*

Same Query runs fine in the original database but fails with dblinks. Is it possible to run spatial operations through dblink?

View 4 Replies View Related

SQL & PL/SQL :: ORA-31603 When Using DBMS_JOB.SUBMIT

Jul 5, 2013

I'm using dbms_metadata.get_ddl inside a package and I have a problem: if I execute the package directly, like "exec pkg_util.read_ddl('TA_DL_IDP', 'RMI_KUNDE') it works fine.

If I submit the same procedure using DBMS_JOB.SUBMIT, I get an error: Err_ -31603 - MSG: ORA-31603: object "RMI_KUNDE" of type TABLE not found in schema "TA_DL_IDP"

I know that in order to use dbms_metadata.get_ddl I need to have SELECT_CATALOG_ROLE and I know about the necessity to give explicit grants to objects rather than using roles when running pl/sql code from inside a package, but this is different: the different behavior is between running the same package in foreground and submitting it using DBMS_JOB.SUBMIT.

View 1 Replies View Related

SQL & PL/SQL :: DDL Though Dbms_metadata

Dec 1, 2011

I have extracted ddl for table using dbms_metedata packages. it always provide douple quotes(") in for every word in ddl. I need to remove " from the ddl scripts.

View 8 Replies View Related

SQL & PL/SQL :: Schema Compare Using Dbms_metadata.compare_alter

Apr 6, 2010

I want to compare two schema and find out object differences and generate DDL script to make schema1 like schema2. I was wondering what is the easiest way to use dbms_metadata.compare_alter to compare all tables. I know its possible through procedure but I am new to sql.

View 2 Replies View Related

SYS.DBMS_METADATA - Error After Migrate To 11gr2

Feb 21, 2013

I am receiving a SYS.DBMS_METADATA error after migrating to 11gr2 RAC from 10gr2 RAC. its worked well in 10g but now throwing error.

onnected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select object_name,object_type from dba_objects where object_name='STUDENT';
OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
STUDENT            SYNONYM
STUDENT            MATERIALIZED VIEW
STUDENT            TABLE
[code]....

ORA-06512: at line 1How could we resolve this error. I have already given select_catalog_role to SCOTT but id did not work.

View 5 Replies View Related

Server Administration :: Dbms_metadata Giving Incorrect Results?

Jun 20, 2011

nlsb> select file_name,bytes from dba_data_files where tablespace_name='ARIAN_DATA';

FILE_NAME BYTES
------------------------------------------------------------ ----------
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf27lcn_.dbf 5368709120
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1txnm_.dbf 8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1tr6v_.dbf 8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1tonc_.dbf 8589934592

[code].....

dbms_metadata is giving me code that will create more datafiles than the original, and furthermore won't run: firstly, because two files are named with a null string, and secondly because it includes RESIZE commands which won't work because they nominate OMF file names. Or is dbms_metadata unreliable?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved