Data Pump IMPDP failed: ORA-31655: no data or metadata objects selected for job and ORA-39154


Error Description:
I have a dump file which is taken from different database with DBA user. I am trying to import the same dump to different database with different user.  Data Pump import job failed with following error.

ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import
Solution Description:

It is the permission / privilege issue with the user which trying to import the dump. The user doesn’t have IMP_FULL_DATABASE privilege to import the dump. You have to grant above privilege to the user which will resolve the issue. See one example below.

impdp full=y  directory=exp_dir dumpfile=exp_tab.dmp logfile=imp_exp_tab.log

Import: Release 11.2.0.2.0 - Production on Thu Nov 8 02:57:36 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: sigmatest
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import
Master table "SIGMATEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SIGMATEST"."SYS_IMPORT_FULL_01":  sigmatest/******** full=y directory=exp_dir dumpfile=exp_tab.dmp logfile=imp_exp_tab.log
Job "SIGMATEST"."SYS_IMPORT_FULL_01" successfully completed at 02:57:43
Granting the privilege to the user sigmatest by connecting as DBA user. Then the import succeeded.

SQL> grant imp_full_database to sigmatest;

Grant succeeded.

impdp full=y  directory=exp_dir dumpfile=exp_tab.dmp logfile=imp_exp_tab.log

Import: Release 11.2.0.2.0 - Production on Thu Nov 8 03:53:33 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: sigmatest
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SIGMATEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SIGMATEST"."SYS_IMPORT_FULL_01":  sigmatest/******** full=y directory=exp_dir dumpfile=exp_tab.dmp logfile=imp_exp_tab.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP"                             8.578 KB      14 rows
Job "SIGMATEST"."SYS_IMPORT_FULL_01" successfully completed at 03:53:43
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google