describe on remote synonym fails

Hi guys,

We've built on a remote database special user with synonym to a table of the main schema in the remote database.
In the current database,we've built a remote synonym to the synonym of the special remote database's user.
The strane thing is :

SQL> desc VW_HADAS_KABLANIM;
ERROR:
ORA-04043: object OVDIM.VW_HADAS_KABLANIM does not exist

SQL> select count (*) from VW_HADAS_KABLANIM;

COUNT(*)
----------
148


DESCRIBE fails and regular select works.

Does anyone face with this kind of problem?

Both databases are 8.1.7.3 on HP-UX.

Regards,

Nir

Are the 2 statements executed under same user?

If not therz permission issue.

Abhay.

Both statements are issued by the same user!

Quote:

---

Originally posted by nir_s
Both statements are issued by the same user!

---

Again Oracle going crazy.

I hope not, guess some moderators here give some light on this.

Surely,it is a big mystery.....
May be it's a bug in sql plus.

what is the output of

select owner,object_name,object_type from al_objects where object_name like 'VW_HADAS_KABL';

Run this as sys user.

Quote:

---

Originally posted by adewri
what is the output of

select owner,object_name,object_type from al_objects where object_name like 'VW_HADAS_KABL';

Run this as sys user.

---

Amar :

See his post

Quote:

---

In the current database,we've built a remote synonym to the synonym of the special remote database's user.

---

so o/p would be public(if defined so), 'VW_HADAS_KABL', Synonym.

Rather you could have asked for

Select * from dba_synonyms where synonym_name like 'VW_HADAS_KABL'

or rather u really wanted to know the owner and type of object

u could try somthing like this ( if its DB Link )

select owner,object_name,object_type from all_objects@db_link_name where object_name='VW_HADAS_KABL';


Thanks
Abhay.

The query as user "HADAS", the owner of the remote synonym:

SQL> select owner,object_name,object_type from all_objects@ovdim
2 where object_name like 'VW_HADAS_KABL%';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------
OVDIM VW_HADAS_KABLANIM VIEW
OVDIM_HADAS VW_HADAS_KABLANIM SYNONYM

The query ,as user sys:
SQL> show user
USER is "SYS"
SQL>
SQL> select owner,object_name,object_type from all_objects
where object_name like 'VW_HADAS_KABL';


OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------
HADAS VW_HADAS_KABLANIM SYNONYM


As user HADAS:
SQL> show user
USER is "HADAS"
SQL> desc VW_HADAS_KABLANIM
ERROR:
ORA-02019: connection description for remote database not found


SQL> select count (*) from VW_HADAS_KABLANIM ;

COUNT(*)
----------
158

SQL> select
SYNONYM_NAME,TABLE_OWNER ,TABLE_NAME ,DB_LINK
2 from user_synonyms
3 where SYNONYM_NAME ='VW_HADAS_KABLANIM';

SYNONYM_NAME TABLE TABLE_NAME DB_LINK
------------------------------ ----- ---------------------- ----------
VW_HADAS_KABLANIM VW_HADAS_KABLANIM OVDIM

Now this is again crazy
First post

Quote:

---

SQL> desc VW_HADAS_KABLANIM;
ERROR:
ORA-04043: object OVDIM.VW_HADAS_KABLANIM does not exist

---

second post

Quote:

---

SQL> desc VW_HADAS_KABLANIM
ERROR:
ORA-02019: connection description for remote database not found

---

All i can say is drop the synonym in current DB and also DB link

and recreate the sysnonym as public and also the DB link as public.

Abhay.

Still does not working:

SQL> connect system@hp64_dev1
Enter password: *****
Connected.

SQL> drop synonym HADAS.VW_HADAS_KABLANIM;

Synonym dropped.



SQL> connect hadas/hadas@hp64_dev1
Connected.
SQL> drop database link OVDIM.WORLD;

Database link dropped.

SQL> drop database link OVDIM;

Database link dropped.

SQL> connect system@hp64_dev1
Enter password: *****
Connected.

SQL> create public database link ovdim_hadas
2 connect to OVDIM_HADAS identified by OVDIM_HADAS using 'hp65_dwprod1';

Database link created.

SQL> select count (*) from user_objects@OVDIM_HADAS;

COUNT(*)
----------
12

SQL> create public synonym VW_HADAS_KABLANIM for VW_HADAS_KABLANIM@OVDIM_HADAS;

Synonym created.

SQL> show user
USER is "SYSTEM"
SQL> select count (*) from VW_HADAS_KABLANIM;

COUNT(*)
----------
158

SQL> desc VW_HADAS_KABLANIM;
ERROR:
ORA-02019: connection description for remote database not found

Quote:

---

Originally posted by nir_s
Still does not working:
SQL> desc VW_HADAS_KABLANIM;
ERROR:
ORA-02019: connection description for remote database not found

---

I got the reason why its behaving like this. PS below demo

Code:

---

SQL> alter table emp3 rename to emp;

Table altered.

SQL> select * from dba_synonyms where synonym_name='TEST_DESC'; -- Synonym Test_Desc created as test_desc@oemrep

OWNER                          SYNONYM_NAME                  TABLE_OWNER                    TABLE_NAME                    DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------
PUBLIC                        TEST_DESC                                                    TEST_DESC                      OEMREP

SQL> desc emp -- Current DB
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                            NUMBER
 EMP_NAME                                                      VARCHAR2(11)
 ADDRESS                                                        VARCHAR2(100)
 NICK_NAME                                                      VARCHAR2(15)

SQL> desc test_desc -- this is describing current DB's emp table ( but table name taking from remote DB )
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                            NUMBER
 EMP_NAME                                                      VARCHAR2(11)
 ADDRESS                                                        VARCHAR2(100)
 NICK_NAME                                                      VARCHAR2(15)

SQL> select * from dba_synonyms@oemrep where synonym_name='TEST_DESC'; -- Remote DB's synonym Test_Desc

OWNER                          SYNONYM_NAME                  TABLE_OWNER                    TABLE_NAME                    DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------
SCOTT                          TEST_DESC                      SCOTT                          EMP

SQL> desc test_desc@oemrep -- This will give you correct description coz u r using @
 Name                                                                                                                                                 
 ----------------------------------------------------------------------------------------------------------------------------------------------------
 EMPNO                                                                                                                                                 
 ENAME                                                                                                                                                 
 JOB                                                                                                                                                   
 MGR                                                                                                                                                   
 HIREDATE                                                                                                                                             
 SAL                                                                                                                                                   
 COMM                                                                                                                                                 
 DEPTNO                                                                                                                                               

SQL> desc emp@oemrep -- Table on remote DB.
 Name                                                                                                                                                 
 ----------------------------------------------------------------------------------------------------------------------------------------------------
 EMPNO                                                                                                                                                 
 ENAME                                                                                                                                                 
 JOB                                                                                                                                                   
 MGR                                                                                                                                                   
 HIREDATE                                                                                                                                             
 SAL                                                                                                                                                   
 COMM                                                                                                                                                 
 DEPTNO         

SQL> select count(*) from emp; -- Current DB count

            COUNT(*)
--------------------
            1000001

SQL> select count(*) from test_desc; -- Giving correct results as that of remote DB.

            COUNT(*)
--------------------
                  14
                           

SQL> select count(*) from emp@oemrep;

            COUNT(*)
--------------------
                  14

SQL> alter table emp rename to emp3;

Table altered.

-- Now i desc the synonym

SQL> desc test_desc

-- Other oracle DB goes down...saying windows has generated errors for Oracle.exe.

---

Actually Desc is taking the table name from the datadictionary of the remote DB and describing the current DB table.

Atleast U r lucky u r not getting other DB down as in my case....

regards
Abhay.

Thanks!

I'll check it tommorow again and i'll let you know.

Regards,

Nir

Hi,

Unfortunately,it still does not work!!

The current DB
---------------
SQL> select * from dba_synonyms
2 where SYNONYM_NAME='VW_HADAS_KURSIM';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
---------- --------------- --------------- --------------- -----------
HADAS VW_HADAS_KURSIM VW_HADAS_KURSIM OVDIM
PUBLIC VW_HADAS_KURSIM VW_HADAS_KURSIM OVDIM_HADAS

SQL> select count (*) from user_objects@ovdim_hadas;

COUNT(*)
----------
12

SQL> desc VW_HADAS_KURSIM;
ERROR:
ORA-02019: connection description for remote database not found


SQL> select count (*) from VW_HADAS_KURSIM;

COUNT(*)
----------
97918

SQL> desc VW_HADAS_KURSIM@OVDIM_HADAS;
Name Null? Type
----------------------------------------- -------- ----------------------------
ZEUT NOT NULL NUMBER(9)
KOURSE NOT NULL NUMBER(3)
MITAARICH NOT NULL DATE
ADTAARICH DATE
KOD_MOSAD NUMBER(3)
TEUR_KOURSE VARCHAR2(15)
TEUR_MOSAD VARCHAR2(15)
KOD_AGAF NUMBER(2)

The DESC works only with the adding of the db link!

The remote DB
--------------
SQL> select * from dba_synonyms
2 where SYNONYM_NAME like 'VW_HADAS_KUR%';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
----------- --------------- --------------- --------------- -----------
KAMPUS VW_HADAS_KURSIM OVDIM VW_HADAS_KURSIM
OVDIM_WEB VW_HADAS_KURSIM OVDIM VW_HADAS_KURSIM
OVDIM_BO VW_HADAS_KURSIM OVDIM VW_HADAS_KURSIM
OVDIM_HADAS VW_HADAS_KURSIM OVDIM VW_HADAS_KURSIM

SQL> connect OVDIM_HADAS/OVDIM_HADAS
Connected.
SQL> desc VW_HADAS_KURSIM
Name Null? Type
----------------------------------------- -------- ----------------------------
ZEUT NOT NULL NUMBER(9)
KOURSE NOT NULL NUMBER(3)
MITAARICH NOT NULL DATE
ADTAARICH DATE
KOD_MOSAD NUMBER(3)
TEUR_KOURSE VARCHAR2(15)
TEUR_MOSAD VARCHAR2(15)
KOD_AGAF NUMBER(2)

SQL> select count (*) from VW_HADAS_KURSIM;

COUNT(*)
----------
97918

Regards,
Nir

Which user are you doing the DESC as on the local DB? Could it be that you are doing it as the user HADAS and so using the private synonym instead of the PUBLIC synonym? If so does the OVDIM database link exists?

Nir :

the object VW_HADAS_KURSIM is a synonym to the view VW_HADAS_KURSIM right? ( remote DB )

Now wat is the underlying table for the view VW_HADAS_KURSIM ? ( remote DB )

Build same type of structure (inculding View) on current DB with the user where you created DB Link.

Then let me know.

Thanks
Abhay.

Hi abhaysk,

The mystery is still huge:

The current hostname is hp64
The current db is dev1

I've created a demo user in another db (dev2) on the same host.
Under the domo user i've created the same synonym and the same db link.
And.... DESC works fine!!

So,maybe there is a problem with dev1 db?

I've checked several suspicious init*.ora parameters which appear in dev1 and not in dev2:
db_domain = world
service_names = dev1.world
remote_os_authent = true

I've changed them as dev2 , but the problem is still valid!

I've also created the demo user on another host on another db and the things work fine.

Mistery,isn't it??

Nir :

I will giv up.

Might be mistery...or might be something we are missing.

Well in my case it was describing the current DB table structure & more interestingly if current DB did not have Table name as that of Remote one then Oracle.exe of Remote DB would geterate errors and Windows will kill that process, hence shuting down remote DB ( Its more Stranger(Misterious) than Urs ).

Might be Moderators(Pando,Marist89,Jmodic,ChisLong) can throw some light.

Thanks
Abhay.