Ora 00942 table or view does not exist ошибка

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;

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";

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');

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;

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;

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');

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');

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));

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;

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);

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.

The ORA-00942: table or view does not exist error indicates that you are trying to run a SQL query that refers to a non-existent table or view, or that the appropriate permissions and privileges do not exist, or that the table and view names are misspelt. The referencing table or view does not exist, or you are attempting to use a table without the appropriate permissions or privileges. Check that the table and view names are correctly spelt, and that the view is not being used in areas where a table is required.

The table or view is either not created or it is removed from the database. When you try to execute the table, oracle shows error message as table or view does not exist. It’s possible that the database user lacks the necessary permissions to execute the table or view. If Oracle cannot find the table in the database, the error “ORA-00942: table or view does not exist” will be thrown.

The table or view did not exist because it was never created, which is a typical cause of the “ORA-00942: table or view does not exist.” error. If you’re not sure whether a view, table, or synonym exists, use the data dictionary to get a list of all tables and views. You’re referring to a table or view in another schema that wasn’t created by you. You must refer to the table by its schema name when running the query from another schema.

ORA-00942: table or view does not exist error occurs if the table or view does not exist in the database or that the appropriate permissions and privileges do not exist, or that the table and view names are misspelt.

When the ORA-00942 error occurs

If the table or view is not created or removed from the database, the Oracle error “ORA-00942: table or view does not exist” occurs. The error will be thrown if the table or view does not have the necessary permissions and privileges. The employee table is not created in the database in the following example. The select sql query is executed on the database’s employee table. The employee table could not be found in the Oracle database. As a result, Oracle throws an error stating that the table or view does not exist.

select * from emp;

Error starting at line : 35 in command -
select * from emp;
Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

Root Cause

The table or view that was requested does not exist in the database. The database table or view was either not created or was removed. The sql query searches for a table or view in the database objects list. The SQL error will be thrown because the table or view could not be found in the database. It’s possible that the user that executes the sql query lacks the necessary table or view permissions. If the table does not exist in the database, the user does not have the necessary privileges and permissions to execute, or the table name is misspelt, the ORA-00942: table or view does not exist error will be thrown.

Solution 1

The table or view name in the sql query may be misspelt. Check the table and view names in the sql query. If the name is misspelt, update it to the correct name in the sql query. The misspelt table or view will not be present in the database sql. The ORA-00942: table or view does not exist error will be thrown. Oracle searches the database for the specified table name. The table with the misspelt name does not exist in the database. The error will be thrown by the Oracle database.

Solution 2

The error is typically caused by a table or view not being created or deleted from the database. If the table or view does not already exist in the database, create it. The sql query will run without the error “ORA-00942: table or view does not exist” when the table or view is created in the database. This query cannot be executed if the table has been deleted. Remove the sql query that will be executed in the database. If the table is accidentally removed, recreate it and then run the query to resolve the error.

create table employee
(
empid integer primary key,
me-name varchar2(100)
);

select * from employee;

Solution 3

The table or view can be created using multiple schemas. It’s possible you’re switching schemas. The sql query was unable to find the table or view from the existing schema. In this case, the schema name should appear before the table name. The current schema must have the necessary permissions in order to call a table or view from another schema. The schema name should be prefixed by the name of the table or view. Before running the table from another schema, the necessary permissions should be granted.

select * from <schema_name>.<tablename>;

select * from hr.employee;

Solution 4

When the view is created, the underlying table may be unavailable or removed, or it may not have the necessary permissions to execute. In this case, the error will be thrown. Check that the underlying database objects, such as tables, types, and sequences, are accessible and have the necessary permissions.

create table employee 
(
empid integer primary key,
empname varchar2(100)
);

create view emplist as select * from employee;

drop table employee;

select * from emplist;

Solution 5

If the table is created using a sequence that does not have sufficient permission to execute, the error will be thrown. The table is created using the sequence. If you insert a row into the table, the table will be unable to run the sequence and determine the next value to enter into the auto increment column.

create sequence seq_emp;
create table employee 
(
empid integer default seq_emp.nextval primary key,
empname varchar2(100)
);

insert into employee('Yawin');

The below permissions should be added to execute the sequence if an another user executes the insert statement.

grant select on seq_emp to emp2;