advertisements
_____________________________________________________________________________________________________________________
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_____________________________________________________________________________________________________________________
0 comments:
Post a Comment