thomgreen
asked on
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?
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?
ASKER
yes. I created a public synonym.
Perhaps a public synonym by name delete_stuff is referring to a table in another schema that does not have select privilege.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I fixed the problem... thank for the help.
select * from all_synonyms where synonym_name='DELETE_STUFF