Ora 00942 table or view does not exist как исправить

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. No Right to Use Table
2. No Right to Reference Table
3. Missing Schema Name
4. Misspelled Table Name
5. Missing Quotes on Table
6. Not a Table Essentially

No Right to Use Table

Let’s see an example, which is to query a table owned by someone else.

SQL> conn sh/sh
Connected.
SQL> select first_name from hr.employees where last_name = 'Chen';
select first_name from hr.employees where last_name = 'Chen'
                          *
ERROR at line 1:
ORA-00942: table or view does not exist

This error pattern is caused by a very elementary concept on using the database system. By default, we cannot use other user’s objects until we are granted to do it, otherwise, we will see ORA-00942. You might think that every user knows the basic knowledge, actually, some don’t.

To know all tables that we can use, please query ALL_TABLES dictionary view with specifying table owner.

SQL> select * from all_tables where owner = 'HR';

As we can see, there’s no matching table from owner HR in this case. That’s why we got ORA-00942 because we don’t have SELECT privilege on that table, we can’t pretend it’s existing in our visual scope.

In fact, not only by querying ALL_TABLES, there’re several ways that can instantly check if the table has been ever granted to you for using.

Solutions to ORA-00942

The solutions to ORA-00942 are simple, you can either ask DBA or the table owner for the object privilege SELECT. In other words, draw it into our visual scope.

SQL> grant select on hr.employees to sh;

System privilege SELECT ANY TABLE is also a cure to ORA-00942, but it may be too strong to be granted to a normal user, especially when the user is used by public database links, it would become a security hole, even though some restrictions are imposed on database links.

For DBA, we should keep least required privileges of the account that accesses a database in order to prevent unexpected security vulnerabilities, although it somewhat compromises on fighting ORA-00942.

In some cases, users may need SELECT privileges on all tables of another owner, but granting each of every tables could be a tedious job to do. DBA had better know some tricks about how to grant all tables owned by other user at a time in case of occasional ORA-00942.

View vs Synonym

Let’s study a more advanced case about ORA-00942, which involves DBA who tried to CREATE VIEW for a third party.

SQL> conn / as sysdba
SQL> create view sh.employees as select * from hr.employees;
create view sh.employees as select * from hr.employees
                                             *
ERROR at line 1:
ORA-00942: table or view does not exist

ORA-00942 in here did not mean that DBA has no right to use the table, it was meant for the third user SH in this case. OK, we tried anther way around by creating a synonym for the grantee, SH.

SQL> create synonym sh.employees for hr.employees;

Although the synonym was created successfully without ORA-00942, it’s useless until the base table is granted to the grantee, i.e. to be seen as existing in grantee’s scope. As has been noted, the principle is always the same, the user must have proper privileges to use other’s objects.

What will see if we try to describe the synonym?

SQL> conn sh/sh
Connected.
SQL> desc SH.EMPLOYEES;
ERROR:
ORA-04043: object "HR"."EMPLOYEES" does not exist

In the above, we tried to describe the synonym, but it handed over the base table eventually. Consequently, ORA-04043 was thrown instead of ORA-00942 for the unknown object.

No Right to Reference Table

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.

Missing Schema Name

First of all, we granted SELECT privilege on a table EMPLOYEES of HR to SH, which means SH can query EMPLOYEES even though it does not belong to him.

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

Phenomenon

As the user SH, we tried to query the table.

SQL> conn sh/sh
Connected.
SQL> select first_name from employees where last_name = 'Chen';
select first_name from employees where last_name = 'Chen'
                       *
ERROR at line 1:
ORA-00942: table or view does not exist

Why is ORA-00942? Haven’t we been granted to access the table? This is because we forgot to prefix schema name. Without schema name prefixed, SQL parser will search for the table in the scope of current user, SH. We had better to query USER_TABLES to list all tables that belongs to current user SH for sure.

Solutions to ORA-00942

We can have several solutions to ORA-00942 here, the first one is very straightforward, just prefix the schema name.

1. Prefix Schema Name

SQL> select first_name from hr.employees where last_name = 'Chen';

Prefixing the schema name in every kind of statements could be the safest way to use schema objects, especially when you are using a database link to retrieve data from the remote database. I talked about some examples of using database link including rights and wrongs in another post: DB Link, How and Why

2. Use Private or Public Synonyms

If the schema name must be ignored for some reasons, you can create a synonym, either private or public one for this table.

For current user only, we can create a private synonym for this table:

SQL> conn sh/sh
Connected.
SQL> create or replace synonym employees for hr.employees;

For all users, we can create a public synonym for this table:

SQL> conn / as sysdba
Connected.
SQL> create or replace public synonym employees for hr.employees;

Now we can reissue the statement again without problems, this is because the synonym will be replaced with the base object at run-time.

SQL> select first_name from employees where last_name = 'Chen';

That is to say, ORA-00942 can be solved without changing statements by using a synonym. We can save our time on modifying the original programs.

3. Switch Working Schema Name

If we would like to use other’s schema for the rest of SQL statements, we can switch current schema to the owner by this:

SQL> alter session set current_schema=hr;

In this way, we set the current schema as HR, so we can ignore the schema name for rest of statements. In other words, we don’t need to prefix schema name to solve ORA-00942, because our working schema has been changed.

SQL> select first_name from employees where last_name = 'Chen';

That’s the trick to avoid ORA-00942 without changing statements in your programs, just don’t forget to revert the setting afterwards.

Misspelled Table Name

We might think we were right, but eventually, we were wrong. For example:

SQL> select first_name from hr.emlpoyees where last_name = 'Chen';
select first_name from hr.emlpoyees where last_name = 'Chen'
                          *
ERROR at line 1:
ORA-00942: table or view does not exist

Do you notice that? I misspelled the table name deliberately, as a result, ORA-00942 was our company. In fact, misspelling happens all the time. A misspelled table or view of course is not existing. Just like we wanted to type «like», but we typed «liek» eventually.

Solutions to ORA-00942

Please make sure the identifier is correct by querying ALL_TABLES to list the correct table name that we can use, even though they belong to other users.

SQL> select table_name from all_tables where owner = 'HR';

If you found nothing wrong, just call someone else to help you to identify the mistake. It’s especially helpful when you cooperate with a senior developer.

Some GUI tools like Toad, PL/SQL developer or SQL developer can also reduce ORA-00942 by automatically completing the object name in their SQL editors. For sqlplus users, sorry, it does not have any spell checker.

Missing Quotes on Table

Seriously, I have talked about database object naming rules a lot including non-quoted and quoted name and how to use them properly. This is exactly the same case related to naming rules.

First of all, we create a table small in exact form by quoting the name.

SQL> create table "small" (c1 int);

Phenomenon

Then, we tried to query this new table by this:

SQL> select * from small;
select * from small
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Without quoting table name, the query was looking for an upper-cased table named SMALL by default, not small from SQL parser’s eyes. Consequently, SQL parser found nothing existing and threw ORA-00942 to notify this issue. This might sound strange, but this is how the exact form, the quoted naming rule works.

Solutions to ORA-00942

Just use exactly the same quoted name as we provided in table definition.

SQL> select * from "small";

Good! At least we have no ORA-00942 this time.

For proving this, we may check the original table name by this query.

SQL> select table_name from user_tables order by 1;

Eventually, it’s a lower-cased table comparing to normal upper-cased tables. Furthermore, we can create a table with a case-insensitively same name.

SQL> create table small (c1 int);

There is no trick, even though I used an lower-cased name without quotes in the statement, an upper-cased table named SMALL was created which differs from small within the namespace of current user.

SQL> select table_name from user_tables order by 1;

Also, there is no name collision, no ORA-00942 as long as they are essentially different.

Not a Table Essentially

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