Skip to content
Home » Oracle » How to Resolve ORA-00942: table or view does not exist

How to Resolve ORA-00942: table or view does not exist

ORA-00942

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)
  2. This may also apply to the following statements.

    • INSERT
    • UPDATE
    • DELETE
    • CREATE VIEW
    • GRANT SELECT ON
  3. ALTER TABLE
  4. 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.

TOAD Error ORA-00942: Table or View Does not Exist
TOAD Error ORA-00942: Table or View Does not Exist

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                 Rowe
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.

4 thoughts on “How to Resolve ORA-00942: table or view does not exist”

  1. I have a schema on a test evironment called icbs. I want to delete it and created again to import dmp to it. While droping user icbs on sql plus (drop user icbs CASCADE;) I got this error table or view does not exist.i am tying to find a solutiom with no luck from one and half week. When running (select count (*) from all_objects when owner = ‘icbs’ i got 0

    1. To look up what existing objects of a user, you should use the upper-cased name. For your case, you should use:

      select count (*) from all_objects where owner = 'ICBS';

Leave a Reply

Your email address will not be published. Required fields are marked *