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.
You can check out the structure of the TAB view just by using DESCRIBE command, Like this
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.
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.