Oracle Tips and Techniques

ORA-00942: Table or View does not exist
Cause of error: Oracle table or view does not exist in the database.
Oracle database privileges are missing or granted incorrectly.

Actions to take: Make sure that the table or view or synonym that you are referencing exists in the database and ensure they have necessary privileges.

For example if you try to access a table or view from another schema and you do not have select privilege to that table or view then you will get this error message. Even if you specify the table or view name using the schema name- you will still get this error.

Lets’ say there is a table called table1 in a schema called schema1. There is another schema called schema2. At the moment you are in Oracle schema2 and want to execute an Oracle select statement on table1 such as

select * from schema1.table;

But if you do not have Oracle select privilege granted to you then you will get ORA-00942.

To fix that login to Oracle in your schema1 schema and then run the following statement to grant select privilege to schema2.

SQL> grant select on schema1.table1 to schema2;

Now you should be able to access table1 data from schema2 as long as you qualify the Oracle table name with the schema name i.e. schema1.table1. This query should return you data now.

select * from schema1.table;

However you may find that specifying the schema name of the table owner each time is quite problematic/time consuming – i.e. the schema name is not easy to spell or quite long. In that case you can to use Oracle synonyms.

Creating an Oracle synonym allows you to specify the name of an Oracle object from another Oracle schema as if they are in your current schema.

To know how it works / see example Click here for Oracle Synonym examples

I hope this post helped. Please leave your comments below.

Filed under: Oracle Errors by admin