dblink error ORA-00942 & ORA-02063

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?
Who is Participating?
MohanKNairConnect With a Mentor Commented:
Create aprivate synonym instead of public synonym. Also specify the DB Link name wheb creating the synonym

Are there any synonyms for the table delete_stuff.

select * from all_synonyms where synonym_name='DELETE_STUFF';
thomgreenAuthor Commented:
yes. I created a public synonym.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Perhaps a public synonym by name delete_stuff is referring to a table in another schema that does not have select privilege.
thomgreenAuthor Commented:
The synonym code is below:

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.

thomgreenAuthor Commented:
I fixed the problem... thank for the help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.