Troubleshooting ORA-00942: ORA-04043:

Whenever you get ORA-00942 or ORA-04043, don't be disappointed or blame oracle that it may produce something wrong. Trust oracle and diagnosis the problem. I will try to write a list of actions that may help you to solve these problems.

The error comes in this format,
ORA-00942: table or view does not exist
ORA-04043: object %s does not exist

Cause of The Problem:
--------------------------
1)The object actually does not exist. That means you have done spell mistake. Review your object name. There may be a lower-upper case problem.

2)The table may exist but it is in different schema.

3)The user don't have necessary permission to read the object.

Solution of The problem:
--------------------------
1)Correct the name of the object if there is any spelling mistake. Be aware about case. I will write one example about case.

SQL> create table "TesT" ( a NUMBER);
Table created.

SQL> drop table TesT;
drop table test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> drop table "TesT";
Table dropped.

Here I created a table "TesT". Whenever I wrote TesT it showed it does not exist. Because in SQL*Plus whenever we write any command it takes as all upper case if you don't specify any quote. So to make it case sensitive we explicitly need to write text within double quote. ""

Also check whether blank line exist in the table name.

SQL> create table "TEST "( a number);
Table created.

SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00942: table or view does not exist

You can whether ay blank line exist or not by checking its length or in following way.
SQL> select '*'||table_name||'*' from user_tables where upper(table_name) like '%TEST%';
*TEST *
SQL> select table_name,length(table_name) from user_tables where upper(table_name) like '%TEST%';

TABLE_NAME LENGTH(TABLE_NAME)
------------------------------ ------------------
TEST 5

To drop it or use it you have to use "TEST "
Like,
SQL> drop table "TEST ";
Table dropped.

2)If you have not done any spell mistake then check the owner of the table. You can do it by quring all_tables view.

SQL> SELECT OBJECT_NAME,OWNER,OBJECT_TYPE FROM ALL_OBJECTS WHERE UPPER(OBJECT_NAME)=UPPER('&Type_Object_name_here');

Then access the object as owner.object_name

3)If you fail then it is possibly the reason that you don't have permission on it. So query in DBA_OBJECTS view in order to find that who is the owner and check session privilege from SESSION_PRIVS view.

SQL> SELECT OBJECT_NAME,OWNER,OBJECT_TYPE FROM DBA_OBJECTS WHERE UPPER(OBJECT_NAME)=UPPER('&Type_Object_name_here');

SQL> SELECT * FROM SESSION_PRIVS;

Related Documents:
-------------------------------
Select any Table Select Any Dictionary Permission