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; Continue reading →