-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Both statements are issued by the same user!
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
Last edited by adewri; 04-14-2003 at 04:54 AM.
Amar
"There is a difference between knowing the path and walking the path."
-
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
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.
Last edited by abhaysk; 04-14-2003 at 05:07 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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
SQL> desc VW_HADAS_KABLANIM;
ERROR:
ORA-04043: object OVDIM.VW_HADAS_KABLANIM does not exist
second post
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|