Sql ошибка таблицы не существует

Because this post is the top one found on stackoverflow when searching for «ORA-00942: table or view does not exist insert», I want to mention another possible cause of this error (at least in Oracle 12c): a table uses a sequence to set a default value and the user executing the insert query does not have select privilege on the sequence. This was my problem and it took me an unnecessarily long time to figure it out.

To reproduce the problem, execute the following SQL as user1:

create sequence seq_customer_id;

create table customer (
c_id number(10) default seq_customer_id.nextval primary key,
name varchar(100) not null,
surname varchar(100) not null
);

grant select, insert, update, delete on customer to user2;

Then, execute this insert statement as user2:

insert into user1.customer (name,surname) values ('michael','jackson');

The result will be «ORA-00942: table or view does not exist» even though user2 does have insert and select privileges on user1.customer table and is correctly prefixing the table with the schema owner name. To avoid the problem, you must grant select privilege on the sequence:

grant select on seq_customer_id to user2;

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:

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:

ORA-00942 means that SQL engine found no table or view in your usable scope. In other words, table or view does not exist. The usable scope is a range which defines what tables and views you can use and how you can use them.

In reality, almost every SQL developers have ever seen the error before. The real causes of ORA-00942 may be varying from case to case though.

Now let’s take a look at some error patterns of ORA-00942 and their solutions described in the following sections.

1. SELECT (Query)

This may also apply to the following statements.

• INSERT
• UPDATE
• DELETE
• CREATE VIEW
• GRANT SELECT ON
2. ALTER TABLE

This may also apply to the following statements.

• DROP TABLE
• ALTER TABLE ADD COLUMN
• ALTER TABLE ADD CONSTRAINT
• ALTER TABLE MOVE

SELECT (Query)

This may also apply to the following statements.

• INSERT
• UPDATE
• DELETE
• CREATE VIEW

Usually, we see ORA-00942 in SELECT statements. For example, we select a table which belongs to other user in SQL*Plus.

SQL> show user
USER is "SH"
SQL> select count(*) cnt from hr.lottery_list;
select count(*) cnt from hr.lottery_list
                            *
ERROR at line 1:
ORA-00942: table or view does not exist

Or in any database connection tools like Toad for Oracle.

Here we take the following steps to solve ORA-00942 in SELECT statements.

1. Simple Test
2. Enter Values
3. Check Result
4. Synonym Problem

Simple Test

You can use a simple query to test whether you have used the right way to access the table or not.

select ‘»‘ || owner || ‘».»‘ || object_name || ‘»‘ use_this from all_objects where object_type in (‘TABLE’, ‘VIEW’) and lower(owner) = lower(‘&owner’) and lower(object_name) = lower(‘&table_name’);

Enter Values

After issuing the above SQL statement, the tool you use will ask you two substitution values.

Enter owner of the table.

Enter value for owner: hr

Enter the table name.

Enter value for table_name: lottery_list

Then we’ll see the result.

Check Result

There’re only 2 possible results.

Returns Nothing

If it returns nothing or «no rows selected«, then you need to ask for the owner of the table or DBA to grant SELECT privilege to you.

GRANT SELECT ON <OWNER>.<TABLE_NAME> TO <GRANTEE>;

Returns Something

If it does return something like the following:

USE_THIS
----------------------------------------
"HR"."lottery_list"

Then you can use (copy / paste) the result in your statement.

SQL> select count(*) cnt from "HR"."lottery_list";

       CNT
----------
       107

The points to use the table correctly are:

• Make sure the table name is correctly spelled.
• Prefix owner’s name if the table is not yours.
• Enclose the table name by a pair of double quotes if the identifier is case-sensitive.

Synonym Problem

If your query still failed with ORA-00942, please make sure that the table you thought is really a table or a synonym. Let’s see a case.

SQL> show user
USER is "HR"
SQL> select * from customers;
select * from customers
              *
ERROR at line 1:
ORA-00942: table or view does not exist

What message didn’t tell is the base table of the synonym. Let’s check the base table of the synonym.

SQL> select '"' || table_owner || '"."' || table_name || '"' use_this from all_synonyms where lower(synonym_name) = lower('customers');

USE_THIS
----------------------------------------
"OE"."CUSTOMERS"

The synonym could be public or private, it doesn’t matter. In either situation, you simply need the SELECT object privilege on the base table by the owner or a privileged user.

SQL> show user
USER is "SYSTEM"
SQL> grant select on "OE"."CUSTOMERS" to hr;

Grant succeeded.

We fixed the synonym problem.

ALTER TABLE

This may also apply to the following statements.

• DROP TABLE
• ALTER TABLE ADD COLUMN
• ALTER TABLE ADD CONSTRAINT
• ALTER TABLE MOVE

Now we turn to some more advanced topics.

There’re only 2 error patterns of ORA-00942 in ALTER TABLE statement.

1. Not a Table
2. No REFERENCES Privilege

Not a Table

Some database objects may act like tables, but they are not tables essentially. Here is a sample object named HAPPY_EMPLOYEES.

SQL> select first_name, last_name from happy_employees;

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Nancy                Greenberg
Daniel               Faviet
John                 Chen
Ismael               Sciarra
Jose Manuel          Urman
Luis                 Popp

6 rows selected.

ORA-00942 when ALTER TABLE

Let’s see an example of ALTER TABLE.

SQL> alter table happy_employees move;
alter table happy_employees move
*
ERROR at line 1:
ORA-00942: table or view does not exist

The error message told us that it tried to find a table named HAPPY_EMPLOYEES, but nothing is found.

ORA-00942 when DROP TABLE

You can not even DROP TABLE.

SQL> drop table happy_employees purge;
drop table happy_employees purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist

Has the table been removed before our actions? As a matter of fact, the object is not a table, even though it looks like a table. That’s why SQL parser flagged its non-existence problem.

Solutions to ORA-00942

Now, we have to know what the object type it is. A dictionary view USER_OBJECTS can be helpful.

SQL> select object_type from user_objects where upper(object_name) = upper('happy_employees');

OBJECT_TYPE
-------------------
VIEW

As a result, it’s a VIEW. Now the question is: What is the base table? How can we find it? Actually, we can learn the fact by querying USER_VIEWS:

SQL> select text from user_views where upper(view_name) = upper('happy_employees');

TEXT
--------------------------------------------------------------------------------
select first_name, last_name from employees where department_id = 100

Not only views, but synonyms are also schema objects based on tables. That is to say, no matter what you are trying to do is ALTER TABLE or DROP TABLE, you should do it on their base tables in case of ORA-00942.

No REFERENCES Privilege

If your constraint needs to reference a table owned by others, you should get an object privilege called REFERENCES on the table. For example:

SQL> conn sh/sh
Connected.
SQL> create table temp (id number, e_id number, text varchar2(30));

Table created.

SQL> alter table temp add constraint fk_eid foreign key (e_id) references hr.employees (employee_id);
alter table temp add constraint fk_eid foreign key (e_id) references hr.employees (employee_id)
                                                                        *
ERROR at line 1:
ORA-00942: table or view does not exist

Solutions to ORA-00942

To resolve ORA-00942 in such situation, we should grant REFERENCES on the table to grantee like this:

SQL> conn hr/hr;
Connected.
SQL> grant references on hr.employees to sh;

Grant succeeded.

Let’s try to add the foreign key again.

SQL> conn sh/sh
Connected.
SQL> alter table temp add constraint fk_eid foreign key (e_id) references hr.employees (employee_id);

Table altered.

As we can see, a reference constraint that points to another user’s table was added.

SELECT vs REFERENCES

Now it’s time to know some points on the differences between SELECT privilege and REFERENCES privilege.

• SELECT privilege is not the right choice to solve ORA-00942 in such error pattern. As a result of only SELECT privilege presents, you will get ORA-01031 instead of ORA-00942 in this case.
• For convenience, you can grant SELECT object privilege to a role, but you cannot grant REFERENCES to a role, which will fail with ORA-01931.
• There is NO such system privilege called REFERENCE ANY TABLE just like SELECT ANY TABLE available to DBA to grant to. No, not such thing.