Solved

dblink error ORA-00942 & ORA-02063

Posted on 2007-04-09
9
10,243 Views
Last Modified: 2013-12-18
oracle db 10g2:
I have 3 databases, I will call them: db1,db2,db3

o I created dblinks using the same ddl on db2 and db3 that allow me to access db1.
o On db2 and db3 I am using a user with the same name and same privileges and roles - I will call the user bdops
o On am trying to access a table (named delete_stuff) in db1 owned by the user bdopstage.
o From db2 user bdops; I am able to access the 'delete_stuff' table on db1 with no problem.
o From db3 user bdops; I am not able to access the 'delete_stuff' table on db1 with no problem.
I get the following errors:
ORA-00942: table or view does not exist
ORA-02063: preceding line from DB1.WORLD@BDOPSTAGE
:: I get this error when trying to access this new table created (delete_stuff). But I can access almost all of the other tables in the bdopstage schema using this dblink. It would seem as though it is a permission issue but I am having no problem accessing any tables using the dblink on db2.

What would differentiate the 2 db's, db2 and db3, to make one dblink work for all tables and the other dblink to not work with all tables?
0
Comment
Question by:thomgreen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
9 Comments
 
LVL 16

Expert Comment

by:MohanKNair
ID: 18877873
Are there any synonyms for the table delete_stuff.

select * from all_synonyms where synonym_name='DELETE_STUFF';
0
 

Author Comment

by:thomgreen
ID: 18878050
yes. I created a public synonym.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 18878266
Perhaps a public synonym by name delete_stuff is referring to a table in another schema that does not have select privilege.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:thomgreen
ID: 18878512
The synonym code is below:
CREATE PUBLIC SYNONYM delete_stuff
  FOR BDOPSTAGE.DELETE_STUFF

Check out my original posting real quick. I can select from dual and almost all other tables... i can't select on the 'delete_stuff' table (through the dblink) nor can I select from other new tables that I create directly on that db1.

I thought that it might be a permissions issue but I can access all tables through the dblink from db2. Is there any specifics about that particular table that would not allow me to select from db3?

I figured that maybe the roles where not usable through a dblink so I made sure that the 'BDOPSTAGE' user that I was linking to had all of the necessary privileges directly granted to itself.

I am truly confused by this.

0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 500 total points
ID: 18878606
Create aprivate synonym instead of public synonym. Also specify the DB Link name wheb creating the synonym

CREATE SYNONYM delete_stuff FOR BDOPSTAGE.DELETE_STUFF@<db_link_name>
0
 

Author Comment

by:thomgreen
ID: 18878822
I fixed the problem... thank for the help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question