ORA-06564: object does not exist

I got ” ORA-06564: object does not exist ”  error in Oracle database.

ORA-06564: object does not exist

Details of error are as follows.

ORA-06564: object string does not exist

Cause: The named object could not be found. Either it does not exist or you do not have permission to access it.

Action: Create the object or get permission to access it

object string does not exist

This ORA-06564 error is related to the named object could not be found. Either it does not exist or you do not have permission to access it.

To solve this error, Create the object or get permission to access it as follows.

SQL>select * from dba_directories where directory_name='DATAPUMP_DIR';
SQL>create or replace directory DATAPUMP_DIR as '/export/dump';
SQL>exit

mkdir -p /export/dump
chown -R oracle:oinstall /export/dump

If you got this error for any  table, you can try to give permission then try again.

GRANT privileges ON object TO user;

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA.TABLE_NAME TO USERNAME;

Another case is as follows.

While Selecting From External Table, ora-6564 occurred against the directory object.

ORA-06564: object <object_name> does not exist

The error obtained is:

ORA-06564: object <object_name> does not exist
06564. 00000 - "object %s does not exist"
*Cause: The named object colud not be found. Either it does not exist or you do not have permission to Access it.
*Action: Create the object or get permission to acces it.
Error at line: 3, column: 15

In the database exists the object <object_name> (is a directory), the path is correct and exists in the same machine, and the user has permission to read, write and execute on that directory.

Customer is accessing the external table of User A from User B by setting alter session set current_schema , then it is failing with ORA-06564

The user should have the proper privileges and login as that user and querying on the table will resolve the issue .

External table test case:




C:\Users\\Desktop\test>info.dat

CREATE DIRECTORY ext_tab_dir AS 'C:\Users\Desktop\test';
GRANT READ,WRITE ON DIRECTORY ext_tab_dir TO SCOTT;

SQL> conn scott/tiger;
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE emp (emp_no CHAR(6), last_name CHAR(25), first_name CHAR(20), middle_initial CHAR(1), hire_date DATE, dob DATE);

Table created.

SQL> CREATE TABLE emp_load
2 (employee_number CHAR(5),
3 employee_dob CHAR(20),
4 employee_last_name CHAR(20),
5 employee_first_name CHAR(15),
6 employee_middle_name CHAR(15),
7 employee_hire_date DATE)
8 ORGANIZATION EXTERNAL
9 (TYPE ORACLE_LOADER
10 DEFAULT DIRECTORY ext_tab_dir
11 ACCESS PARAMETERS
12 (RECORDS DELIMITED BY NEWLINE
13 FIELDS (employee_number CHAR(2),
14 employee_dob CHAR(20),
15 employee_last_name CHAR(18),
16 employee_first_name CHAR(11),
17 employee_middle_name CHAR(11),
18 employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy"
19 )
20 )
21 LOCATION ('info.dat')
22 );

Table created.

SQL>

SQL> INSERT INTO emp (emp_no,
2 first_name,
3 middle_initial,
4 last_name,
5 hire_date,
6 dob)
7 (SELECT employee_number,
8 employee_first_name,
9 substr(employee_middle_name, 1, 1),
10 employee_last_name,
11 employee_hire_date,
12 to_date(employee_dob,'month, dd, yyyy')
13 FROM emp_load);

2 rows created.

SQL>
SQL> select * from emp;

EMP_NO LAST_NAME FIRST_NAME M HIRE_DATE DOB
------ ------------------------- -------------------- - --------- ---------
56 aaa bbb a 01-SEP-04 15-NOV-80
87 ccc ddd m 01-JAN-99 20-DEC-70

SQL>
SQL> select * from emp_load;

EMPLO EMPLOYEE_DOB EMPLOYEE_LAST_NAME EMPLOYEE_FIRST_ EMPLOYEE_MIDDLE
----- -------------------- -------------------- --------------- ---------------
EMPLOYEE_
---------
56 november, 15, 1980 aaa bbb xxx 0
01-SEP-04

87 december, 20, 1970 ccc ddd yyy 0
01-JAN-99
SQL>

SQL> conn test_user/****
Connected.
SQL>
SQL>

SQL> show user
USER is "TEST_USER"

SQL>
SQL> alter session set current_schema=SCOTT;

Session altered.

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

SQL> select * from emp_load;
select * from emp_load
*
ERROR at line 1:
ORA-06564: object EXT_TAB_DIR does not exist

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )