Skip to content
Home » Oracle » How to Resolve ORA-04043: object does not exist

How to Resolve ORA-04043: object does not exist

ORA-04043

ORA-04043 means that the schema object that you want to use is not existing, this could be a typo, privilege or case-sensitive problem like we said in solving ORA-00942 due to missing quotes.

Let's see some error patterns that generate ORA-04043.

  1. Lack of SELECT object privilege
  2. Case-sensitive object Name
  3. Import Data by Original IMP
  4. Rename a Table

Lack of SELECT Object Privilege

We got ORA-04043 when describing unusable object which is out of scope in the user's view.

SQL> conn sh/sh
Connected.
SQL> desc hr.employees;
ERROR:
ORA-04043: object hr.employees does not exist

The solution is to grant SELECT object privilege to the user.

SQL> grant select on hr.employees to sh;

Grant succeeded.

Case-sensitive Object Name

If we created a schema object in case-sensitivity by enclosing name with quotations, we should use the name carefully. For example.

SQL> create table "Test1" (id number);

Table created.

When we tried to describe the object, we got ORA-04043.

SQL> desc Test1;
ERROR:
ORA-04043: object test1 does not exist

Actually, the correct way is to enclose the name just like we defined it.

SQL> desc "Test1"
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

We have talked a lot about the interaction between column name and quotation in solving ORA-00904.

Import Data by Original IMP

When I imp a dump file which was exported by the original exp, I found some stored procedures were missing with IMP-00098.

[oracle@primary01 ~]$ imp \"/ as sysdba\" fromuser=ERPAPP rows=n file=/dumps/erpapp_exp.dmp log=/dumps/erpapp_imp.log
...
IMP-00098: INTERNAL ERROR: impccr2
IMP-00017: following statement failed with ORACLE error 4043:
 "ALTER PROCEDURE "GET_PAYMENT_DAY" COMPILE REUSE SETTINGS TIMESTAMP '2017-"
 "03-30:10:47:20'"
IMP-00003: ORACLE error 4043 encountered
ORA-04043: object GET_PAYMENT_DAY does not exist

Solution

Eventually, it's a character conversion problem. Let's check current character set.

[oracle@primary01 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8

It's exactly the same as the database. So next, let's try to use the source character set UTF8 to import objects.

[oracle@primary01 ~]$ export NLS_LANG=AMERICAN_AMERICA.UTF8
[oracle@primary01 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.UTF8

This time, we are able to import missing stored procedures.

Rename a Table

When we tried to rename a table, we saw ORA-04043. Which means, you don't have such table.

SQL> rename employees to employees_bak;
rename employees to employees_bak
*
ERROR at line 1:
ORA-04043: object EMPLOYEES does not exist

Let's check what schema we connected to.

SQL> show user;
USER is "SH"

To rename a table, you should connect to the database as the schema owner.

Please note that, prefixing the schema owner to the object in RENAME SQL statement is no use, it results another error.

Leave a Reply

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