Sql error ora 00942 table or view does not exist

Have you gotten an ORA-00942 error? I’ll explain the cause and the solution of the error in this article.

ORA-00942 Cause

The error message appears when you try to run an SQL statement:

ORA-00942: table or view does not exist

This happens for one of many reasons:

• The statement references a table or view that does not exist
• You do not have access to that table or view
• The table or view belongs to a different schema and you did not refer to the schema name
• You’re running Oracle 12c, using a sequence as a default value, but don’t have select privileges on the sequence.

The cause of the error should be the same in each database version. It shouldn’t matter if you’re getting this “table or view does not exist” error in Oracle 10g, Oracle 11g, or Oracle 12c.

The only difference is the sequence-related cause mentioned above because one of the new features in Oracle 12c is the ability to use a sequence as a default value.

Let’s take a look at some of the solutions, depending on the cause.

There are several solutions for this error, depending on the cause.

First, check that the table exists. You can do that by running this query:

SELECT owner, object_name, object_type
FROM all_objects
WHERE object_type IN ('TABLE','VIEW')
AND object_name = 'OBJECT_NAME';

Substitute the word OBJECT_NAME with your table name. It must be in upper case as well.

SELECT owner, object_name, object_type
FROM all_objects
WHERE object_type IN ('TABLE','VIEW')
AND object_name = 'CLASS';

Results:

If your table does not show, then it does not exist, and you’ll need to look into why it doesn’t exist.

Or, if you’re using SQL Developer, you can check the table exists by expanding the Tables section on the left side of the screen. If you see the table there, it means it exists and you’re the owner.

Next, check the owner of the table.

If the table exists, and you’re getting this error, then check the owner of the table.

You can use the same query as above, and take note of the owner of the table.

If the owner is not you, then you’ll need to contact the database administrator to request privileges to select from the table (or to perform whatever operation you were trying to do).

Finally, check your query to ensure it refers to the correct schema.

If the table or view exists, and you have the privileges you need, then it could be an issue in your query.

Let’s say your username is “bob”. You have a set of tables under the “bob” schema.

If you want to select from a table called “employee”, and this is in the “mary” schema, it is owned by “mary”. When you refer to the table (such as in a SELECT statement), you might have a query like this:

SELECT *
FROM employee;

You might get the ORA-00942 error at this point. This is because Oracle is looking in your schema, or “bob”, for an employee table. But, it doesn’t exist in your schema – it’s in the “mary” schema.

So, you’ll need to change your query to include the schema name.

SELECT *
FROM mary.employee;

This query should run without the error.

Oracle 12c and Sequences

If you’re getting the ora-00942 table or view does not exist in Oracle 12c, then it could be caused by this situation:

• Another user has a table and a sequence
• One of the columns in the table has a default value of the sequence.nextval
• You have the right privileges on the table

However, you can get this error if you’re querying this table and don’t have select privileges on the sequence.

Consider this situation:

As user “bob”:

CREATE SEQUENCE sequence_book_id;

CREATE TABLE books (
  book_id NUMBER(5) DEFAULT sequence_book_d.nextval PRIMARY KEY,
  title VARCHAR2(100)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON books TO "mary";

Now, logged in as “mary”:

INSERT INTO books (title)
VALUES ('The Adventure');

You’ll get an ORA-00942 error here.

The reason for this is that “mary” doesn’t have SELECT privileges on sequence_book_id. She has INSERT privileges on the table, but as a result of inserting into the table, a SELECT on the sequence is called, which causes this error.

To resolve this, grant SELECT privileges to the second user.

GRANT SELECT ON sequence_book_id TO mary;

That should now work.

I hope this article has helped you resolve the ORA-00942 error.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

ORA-00942 is one of the many errors which Oracle developer ,Oracle DBA often gets.

Lets first look at the OERR output

Cause: The Oracle table or Oracle view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required.

Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required to access the table. If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it.

Action: Check each of the following:

• the spelling of the table or view name.
• that a view is not specified where a table is required.
• that an existing table or view name exists.
• Contact the database administrator if the table needs to be created or
  if user or application privileges are required to access the table.

Checklist to run for ORA-00942

(1) If you user owns the table or view ,check for spelling mistakes. Query User_objects to check if the table or view exists

select object_name,object_type from user_objects;



  


                       
  

(2) If you dont own the table or view, check if you have permission for the table or view. Query all_objects to check if the table or view you are querying is having permission

select *
from all_objects
where object_type in ('TABLE','VIEW')
and object_name = '&1';

If it is not shown here, it means you dont have access to that particular view.You have to get access for the view for the table

GRANT privileges ON object TO user;

Following access can be granted

If the synonym is not created , then you have to use the owner.<table_name>  to access the  table

Desc <owner>.<table_name>
select * from <owner>.<table_name>; 

You can create public synonym using the below command

create public synonym exp_table for test.exp_table

(3) ORA-00942 may also occurs while refreshing a Oracle Materialized views

You  will need to check all the tables in the materialized query to find out the issue. You can use sql trace to find that

Sometimes while creating the table,special character get copied ,So you may get this error. Check the table name in all_tables and then act accordingly.

Related Articles
ORA-00936 missing expression
ORA-00904: invalid identifier
ORA-00257: archiver error. Connect internal only, until freed.
ORA-06512 at line num
Oracle Create table
ORA-00911: invalid character
Oracle documentation