dblink error ORA-00942 & ORA-02063

[thomgreen]

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?

 

[SantaMufasa]

Thom,

When you access a remote table via a database link, you are doing so as though you are the owner of the table. Therefore, there should not be any "permission" problem, per se.

If I were in your situation, I would try to isolate the problem by doing the following:

1) (Session 1) while connected as the owner of the table(s), confirm the accessibility of each table of interest with the following SELECTs:

SELECT count(*) from <table_name>;

2) (Session 2) connect as a user that owns a database link to the remote schema in Session 1.

3) In Session 2, execute SELECTs similar to the confirming
SELECTs in step 1:

SELECT count(*) from <table_name>@<dblink to Session 1>;

Please post your findings from Steps 1 and 3 back in this thread.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

Despite your expectation to the contrary, the evidence is that the dblink on db2 is connecting to db1 using a different id than the one used by the dblink on db3. To verify (or refute) this, please log in as bdops and execute the following query on both db2 and db3. Please let us know the results.

select user from dual@db1

 

[karluk]

Scratch that suggestion. My query will give your Oracle credentials from db2 (or db3), not the connection info from db1.