ORA-00942 table or view does not exist Solution

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.

 

ORA-00942 Solution

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:

OWNER	OBJECT_NAME	OBJECT_TYPE
SYSTEM	CLASS	TABLE

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.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: