0

when I do

select *
from all_synonyms
where synonym_name='SYNAAA'

I can see synonym SYNAAA is public, and table behind it is TABAAA in schema A.

Given SYNAA is public, I should be able to query it directly by just select * from SYNAA right? but I got "table or view does not exist" error, I tried to refer it by schemaname.SYNAAA or schemaname.TABAAA, or grant but all failed.

The thing is when I check schema A's table list, there is no table TABAAA under it. Is this the reason I got "table or view does not exist" error? table behind the synonym SYNAA does not exist?

Thank you very much!

2

A synonym is an alternative name for an object.

From Database SQL Language Reference

Specify PUBLIC to create a public synonym. Public synonyms are accessible to all users. However each user must have appropriate privileges on the underlying object in order to use the synonym.

So the synonym is an additional name that you can use to access the object. But you must have still appropriate grants on the base object.

If SYNAAA is a public synonym for the table A.TABAAA and you get an error when you execute

select * from SYNAAA 

then you will get the same error if you execute

select * from A.TABAAA

the error

ORA-00942 table or view does not exist

means either the table A.TABAAA exists but you have no privileges on it or the table does not exist at all. The view all_tables doe not show you all tables of the database but only the tables that you can access. Generally you cannot check if a table does not exist at all nor can you grant yourself privileges on a table of another schema. A DBA can query the dba_tables view to check if the table exists.

select owner, table_name
from dba_tables
where owner='A'
and table_name='TABAAA'

If Oracle raises an error message and you post this here then you hould always post the complete error message, which is

ORA-00942 table or view does not exist

An explanation of the error message can be found in the documentation of your database version, in the documentation of the current version. If there is no explanation you should try the documentation of Oracle 9i. For this error message the 9i manual says

(...) Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted.

  • This would be the most likely answer. Just because the synonym is public, doesn't mean public has been granted read access on the underlying table. – Patrick Mar 28 '18 at 19:38
0

When the synonym was created TABAAA existed and, presumably, you had permissions on it. The possibilities are that the table has now been deleted so the synonym is invalid OR permissions have been modified such that you no longer have permission to see it.

To check if the synonym itself is still valid check in ALL_OBJECTS (or DBA_OBJECTS if you have the privilege);

SELECT object_name, object_type, status
  FROM all_objects
 WHERE object_name = 'SYNAAA'
   AND object_type = 'SYNONYM'

If it's INVALID then the object the synonym points to is not there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.