Re: database link can select but not describe

a.. fact: Oracle Server - Enterprise Edition 8

a.. symptom: Describing a table over database link fails

a.. symptom: ORA-02019: connection description for remote database not found

a.. symptom: Selecting against table works

a.. cause: This may be <<BUG:1530562>> which is base bug <<BUG:703020>& gt;. This is fixed in 8.0.5.2 and 8.0.6.0.

The descriptions of the two bugs are quite different but development has identified bug <<BUG:1530562>>
as being fixed by <<BUG:703020>>.

This bug has been reported in 8.1.5, 8.1.6 and 8.1.7 so check with Oracle Support for possible backports for other versions.

The error is generated because an internal function fails to find a valid remote database associated with the database link.

fix:

Upgrade to version 8.0.5.2 or 8.1.6.0 or check with Oracle Support for possible
backports.

There are two suggested workarounds:

1. Create a view on the tablename_at_dblink and then describe the view.

SQL> Create view as test_view as select * from emp_at_mydb; View created

SQL > desc test_view;

Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

2) Qualify the database link with the db_domain parameter specified in the init.ora file.
Assuming db_domain is testdomain:

SQL> Create database link mydb.testdomain connect to scott identified by tiger
using 'mydb';
Database link created.

SQL> desc emp_at_mydb;

Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

"Simon Cunningham" <cs40_at_gre.ac.uk> wrote in message news:3C501B8B.2080303_at_gre.ac.uk...
> I have users here that have created dblinks that work fine but...you can
> select from it but you cant describe a table.
>
> SIMO>select * from time_at_duma
> 2 ;
>
> TIME
> --------
> 07:23 PM
>
> SIMO>desc time_at_duma
> ERROR:
> ORA-02019: connection description for remote database not found
>
> Am I missing something???
>
Received on Thu Jan 24 2002 - 16:04:38 CST