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;

SQL error ORA-00942: Table Or View Does Not Exist is very common. I was reading your comments the other day. Consequently I saw that some of you guys are having a problem in finding objects like table or views in the schema and facing an error. 

So what does this “ORA-00942: Table or View Does not Exist” error means?

It means exactly what it says, the table or view you are executing your query on does not exist in your schema. To explain, whenever you execute a query which includes a table, view, synonym or a cluster which does not exist into the schema with which you are connected the SQL engine will show you this error.

So how can we solve it?

You can easily solve this error either by creating the missing object (by object I mean a table, view, synonym or a cluster) or by importing it from another source into your schema.

Is there any way we can check what objects do we have in our schema?

Yes, we can easily check out how many objects are created and saved in a schema. In addition to their types and names.

Oracle provides a View which comes pre-created into the SYS schema. And using it we can find out how many objects are there in a schema as well as what are their types and names. The name of that view is TAB.

What is this TAB view?

TAB is a pre-created view which is saved into the SYS schema of the Oracle Database. Furthermore this view stores the name, type and the cluster id of all the tables, views, synonyms and clusters created into the schema of the database.

Do we need to create this view?

No, TAB view is created by Oracle engine during the creation of the database thus you don’t have to worry about it. 

Can we use this view in any schema of the database?

TAB view can easily be accessed by any user/schema of the database through its public synonym. Moreover along with the TAB view, Oracle engine also creates its public synonym with the same name. Which provides the secure access to the TAB view to any schema in the database.

In addition, both the TAB view and its public synonym share the same name which is TAB and the same structure, so don’t be confused.

What is the structure of the TAB view?

TAB view consists of three columns, TNAME, TABTYPE, and CLUSTERID. First two columns are of VARCHAR2 type and hold the name of the database object and their types. Meanwhile the third column is of NUMBER type and holds the Cluster ID of the cluster if you have created any in your schema.

Check out the structure of TAB view here.

Info Byte:
You can check out the structure of the TAB view just by using DESCRIBE command, Like this
DESC TAB;

Ok, I think I have answered almost all the questions about the TAB view. Do you think something is left out or you still have some doubts? Then feel free to write to me on my Facebook or Twitter.

Anyways, now let’s do some examples.

You first need to connect with the schema whose object’s information you want to find out. For example, let’s say I want to find out how many objects are created in HR schema and what are their names. Once you have decided that go ahead and connect your database with that schema.

You can read my blog on how to connect with the database using SQL Developer and SQL*Plus as well.

Before showing you the examples of TAB view let me tell you that I am connected to my database through the HR user. But you can use any of these queries in any user/schema of your desire. They will show you the result accordingly.

Example 1: Show me the complete information about all the tables, views, synonyms and clusters created in my schema.

SELECT * FROM tab;

On successful execution, the above query will show you the name, type and the cluster id of all the tables, views, synonyms and clusters created in the schema with which you are connected.

Example 2: Show me the total number of objects created into my schema.

In order to find out the total number of objects created in a schema, you can use the COUNT function with the TAB view. Like this

SELECT count (*) FROM tab;

On successful execution, this query will show you the total number of objects created in your schema.

Example 3: Show me how many tables are there in the schema and what are their names.

In order to find out the information of a specific object, you can use the WHERE clause and filter the result.

SELECT * FROM tab WHERE tabtype = ‘TABLE’;

This query will show the information of all the objects of the schema which are tables. You can refine this query even more like this:

SELECT tname FROM tab WHERE tabtype = ‘TABLE’;

This will show you only the names of all the tables created into the schema. In case you only want to find out the total number of tables created into the schema then you can modify this query like this:

SELECT count (*) FROM tab WHERE tabtype = ‘TABLE’;

This will show you the total number of tables created in your schema.

Like this you can tweak the queries according to your needs.

Now, if you have gone through the blog carefully then take this poll on my Facebook page and check out your knowledge.