ORA 39166: Object DEPT was not found with EXPDP

ORA 39166: Object DEPT was not found with EXPDP

EXPDP is the utility to take logical backup of the databases, schema, tables and structure.In Oracle, EXPDP is not working with system schema in any mode. When you tried to export the system  schema object then it will result in the following error:

ORA 39166: Object DEPT was not found with EXPDP

@Note: Error occur when objects present in SYS schema and you tried to take an export of the particular objects. When you are EXPDP of SYS objects, the condition is not working for sys user in expdp

1. Create objects in the sys schema create table test ( id number );

2. Expdp the test tables

expdp tables=test directory=backup dumpfile=test.dmp logfile=test.log

3. Get error that test table is not found
ORA: 39166 : Object test was not found
Note: EXPDP is not working with system schema in any mode. So only able to take full sys user schema.

First Solution:

1. You can expdp the full dump of the sys schema.

expdp schemas=sys  directory=dbbackup dumpfile=sysdump.dmp logfile=syslogfile.log

 
Second Solution:

1. If you want specific table backup from the SYS.

2. You can created a new uses like ‘NEW_USER’ in oracle.

create user new_user identified by new_user;
grant connect,resource to new_user;

3. Use CTAS method to created data with following commands:

Create table NEW_USER.table_name as select * from sys.table_name;

4. Export that table from new_user.

expdp tables=new_user.table_name directory=dbbackup dumpfile=tables.dmp logfile=tables.log

5. If you want you can also drop new_user which created for this purpose.

drop user new_user cascade;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s