walkerdba
asked on
export failed
What is the problem with the export?
[oracle@wtu dbs]$ export ORACLE_SID=orcl
[oracle@wtu dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 21:19:34 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1124075876 bytes
Database Buffers 570425344 bytes
Redo Buffers 11608064 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@wtu dbs]$ export ORACLE_SID=pros
[oracle@wtu dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 21:21:05 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 83697664 bytes
Fixed Size 1334660 bytes
Variable Size 75498108 bytes
Database Buffers 4194304 bytes
Redo Buffers 2670592 bytes
Database mounted.
Database opened.
SQL> select username from dbs_users;
select username from dbs_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select username from dba_user;
select username from dba_user
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select username from dba_users;
USERNAME
--------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
TBS
R_CAT
OUTLN
FLOWS_FILES
MDSYS
USERNAME
--------------------------
ORDSYS
EXFSYS
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
USERNAME
--------------------------
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS
ORACLE_OCM
XS$NULL
MDDATA
DIP
APEX_PUBLIC_USER
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
32 rows selected.
SQL> alter user scott account unlock;
User altered.
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
--------------------------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> conn / as sysdba
Connected.
SQL> create directory mydir as '/home/oracle/Desktop/mydi
Directory created.
SQL> desc dba_directories;
Name Null? Type
--------------------------
OWNER NOT NULL VARCHAR2(30)
DIRECTORY_NAME NOT NULL VARCHAR2(30)
DIRECTORY_PATH VARCHAR2(4000)
SQL> select directory_name from dba_directories;
DIRECTORY_NAME
--------------------------
MYDIR
XMLDIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR
SQL> select directory_path from dba_directories;
DIRECTORY_PATH
--------------------------
/ade/b/1191423112/oracle/r
/home/oracle/Desktop/mydir
/u01/app/oracle/admin/pros
/u01/app/oracle/product/11
SQL> host
[oracle@wtu dbs]$
[oracle@wtu dbs]$ cd /home/oracle/Desktop/mydir
[oracle@wtu mydir]$ ls
[oracle@wtu mydir]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 21:47:35 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user moon identified by light;
User created.
SQL> grant create session to moon;
Grant succeeded.
SQL> grant create table to moon;
Grant succeeded.
SQL> alter user moon quota 1M on users;
User altered.
SQL> alter user monn quota 1M on system;
alter user monn quota 1M on system
*
ERROR at line 1:
ORA-01918: user 'MONN' does not exist
SQL> alter user moon quota 1M on system;
User altered.
SQL> grant read,write on directory mydir to moon;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
--------------------------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> host
[oracle@wtu mydir]$ expdp scott/tiger directory=mydir dumpfile=exp.dmp logfile=exp.log
Export: Release 11.2.0.1.0 - Production on Wed Jan 2 22:06:39 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1
[oracle@wtu mydir]$
[oracle@wtu dbs]$ export ORACLE_SID=orcl
[oracle@wtu dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 21:19:34 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1124075876 bytes
Database Buffers 570425344 bytes
Redo Buffers 11608064 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@wtu dbs]$ export ORACLE_SID=pros
[oracle@wtu dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 21:21:05 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 83697664 bytes
Fixed Size 1334660 bytes
Variable Size 75498108 bytes
Database Buffers 4194304 bytes
Redo Buffers 2670592 bytes
Database mounted.
Database opened.
SQL> select username from dbs_users;
select username from dbs_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select username from dba_user;
select username from dba_user
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select username from dba_users;
USERNAME
--------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
TBS
R_CAT
OUTLN
FLOWS_FILES
MDSYS
USERNAME
--------------------------
ORDSYS
EXFSYS
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
USERNAME
--------------------------
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS
ORACLE_OCM
XS$NULL
MDDATA
DIP
APEX_PUBLIC_USER
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
32 rows selected.
SQL> alter user scott account unlock;
User altered.
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
--------------------------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> conn / as sysdba
Connected.
SQL> create directory mydir as '/home/oracle/Desktop/mydi
Directory created.
SQL> desc dba_directories;
Name Null? Type
--------------------------
OWNER NOT NULL VARCHAR2(30)
DIRECTORY_NAME NOT NULL VARCHAR2(30)
DIRECTORY_PATH VARCHAR2(4000)
SQL> select directory_name from dba_directories;
DIRECTORY_NAME
--------------------------
MYDIR
XMLDIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR
SQL> select directory_path from dba_directories;
DIRECTORY_PATH
--------------------------
/ade/b/1191423112/oracle/r
/home/oracle/Desktop/mydir
/u01/app/oracle/admin/pros
/u01/app/oracle/product/11
SQL> host
[oracle@wtu dbs]$
[oracle@wtu dbs]$ cd /home/oracle/Desktop/mydir
[oracle@wtu mydir]$ ls
[oracle@wtu mydir]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 21:47:35 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user moon identified by light;
User created.
SQL> grant create session to moon;
Grant succeeded.
SQL> grant create table to moon;
Grant succeeded.
SQL> alter user moon quota 1M on users;
User altered.
SQL> alter user monn quota 1M on system;
alter user monn quota 1M on system
*
ERROR at line 1:
ORA-01918: user 'MONN' does not exist
SQL> alter user moon quota 1M on system;
User altered.
SQL> grant read,write on directory mydir to moon;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
--------------------------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> host
[oracle@wtu mydir]$ expdp scott/tiger directory=mydir dumpfile=exp.dmp logfile=exp.log
Export: Release 11.2.0.1.0 - Production on Wed Jan 2 22:06:39 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1
[oracle@wtu mydir]$
Last Comment
also have a look at this : http://www.orafaq.com/maillist/oracle-l/2007/01/27/2160.htm
may be some dictionary objects are invalid and hence may be the error.
may be some dictionary objects are invalid and hence may be the error.
you created the directory reference in the database
does the directory also exist on the host
> you can create directories in the database pointing to non-existing host directories
try this before running expdp:
alter system set streams_pool_size=1M;
does the directory also exist on the host
> you can create directories in the database pointing to non-existing host directories
try this before running expdp:
alter system set streams_pool_size=1M;
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
did you grant these roles to scott before exporting?
you have created your own directory and given read/write to the new user "MOON" and you are using scott for export, did u give read/write permission to scott on directory.
and also you have to mention you want schema,table or full DB export.
check this doc for more options on expdp/impdp
http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
you have created your own directory and given read/write to the new user "MOON" and you are using scott for export, did u give read/write permission to scott on directory.
and also you have to mention you want schema,table or full DB export.
check this doc for more options on expdp/impdp
http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
Check the following doc on Oracle Support:
UDE-31623 Error With DataPump Export [ID 1080775.1]
UDE-31623 Error With DataPump Export [ID 1080775.1]
ASKER
Can you tell me what is wrong here...
[oracle@new dbs]$ export ORACLE_SID=data
[oracle@new dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 11:26:05 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL> startup spfile from pfile='/u01/app/oracle/pro
SP2-0714: invalid combination of STARTUP options
SQL> create spfile from pfile='/u01/app/oracle/pro
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
Database mounted.
Database opened.
SQL> alter user scott account unlock;
User altered.
SQL> alter user scott identified by scott;
User altered.
SQL> conn scott/scott
Connected.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
--------------------------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> conn / as sysdba
Connected.
SQL> create or replace directory test as '/home/oracle/Desktop/test
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY test TO scott;
Grant succeeded.
SQL> create user moon identified by light;
User created.
SQL> grant read,write on directory test to moon;
Grant succeeded.
SQL> grant create session to moon;
Grant succeeded.
SQL> grant create session to scott;
Grant succeeded.
SQL> grant create table to moon;
Grant succeeded.
SQL> alter user moon quota 1M on user;
alter user moon quota 1M on user
*
ERROR at line 1:
ORA-02156: invalid TEMPORARY tablespace identifier
SQL> alter user moon quota 1M on system;
User altered.
SQL> alter user moon quota 1M on users;
User altered.
SQL> host
[oracle@new dbs]$ expdp scott/tiger@data tables=EMP,DEPT directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Export: Release 11.2.0.1.0 - Production on Thu Jan 3 11:49:36 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
UDE-12545: operation generated ORACLE error 12545
ORA-12545: Connect failed because target host or object does not exist
[oracle@new dbs]$ expdp scott/tiger@data directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Export: Release 11.2.0.1.0 - Production on Thu Jan 3 11:50:34 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
UDE-12545: operation generated ORACLE error 12545
ORA-12545: Connect failed because target host or object does not exist
[oracle@new dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 11:51:25 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc dba_directories;
Name Null? Type
--------------------------
OWNER NOT NULL VARCHAR2(30)
DIRECTORY_NAME NOT NULL VARCHAR2(30)
DIRECTORY_PATH VARCHAR2(4000)
SQL> select directory_name from dba_directories;
DIRECTORY_NAME
--------------------------
TEST
XMLDIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR
SQL>
[oracle@new dbs]$ export ORACLE_SID=data
[oracle@new dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 11:26:05 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL> startup spfile from pfile='/u01/app/oracle/pro
SP2-0714: invalid combination of STARTUP options
SQL> create spfile from pfile='/u01/app/oracle/pro
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
Database mounted.
Database opened.
SQL> alter user scott account unlock;
User altered.
SQL> alter user scott identified by scott;
User altered.
SQL> conn scott/scott
Connected.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
--------------------------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> conn / as sysdba
Connected.
SQL> create or replace directory test as '/home/oracle/Desktop/test
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY test TO scott;
Grant succeeded.
SQL> create user moon identified by light;
User created.
SQL> grant read,write on directory test to moon;
Grant succeeded.
SQL> grant create session to moon;
Grant succeeded.
SQL> grant create session to scott;
Grant succeeded.
SQL> grant create table to moon;
Grant succeeded.
SQL> alter user moon quota 1M on user;
alter user moon quota 1M on user
*
ERROR at line 1:
ORA-02156: invalid TEMPORARY tablespace identifier
SQL> alter user moon quota 1M on system;
User altered.
SQL> alter user moon quota 1M on users;
User altered.
SQL> host
[oracle@new dbs]$ expdp scott/tiger@data tables=EMP,DEPT directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Export: Release 11.2.0.1.0 - Production on Thu Jan 3 11:49:36 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
UDE-12545: operation generated ORACLE error 12545
ORA-12545: Connect failed because target host or object does not exist
[oracle@new dbs]$ expdp scott/tiger@data directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Export: Release 11.2.0.1.0 - Production on Thu Jan 3 11:50:34 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
UDE-12545: operation generated ORACLE error 12545
ORA-12545: Connect failed because target host or object does not exist
[oracle@new dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 11:51:25 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc dba_directories;
Name Null? Type
--------------------------
OWNER NOT NULL VARCHAR2(30)
DIRECTORY_NAME NOT NULL VARCHAR2(30)
DIRECTORY_PATH VARCHAR2(4000)
SQL> select directory_name from dba_directories;
DIRECTORY_NAME
--------------------------
TEST
XMLDIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR
SQL>
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Export is working fine..
Now I want to import to moon schema..
But it is not working fine...
[oracle@new dbs]$
[oracle@new dbs]$ expdp scott/scott directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Export: Release 11.2.0.1.0 - Production on Thu Jan 3 19:09:08 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/P
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/
Processing object type SCHEMA_EXPORT/TABLE/CONSTR
Processing object type SCHEMA_EXPORT/TABLE/INDEX/
Processing object type SCHEMA_EXPORT/TABLE/COMMEN
Processing object type SCHEMA_EXPORT/TABLE/CONSTR
. . exported "SCOTT"."DEPT" 5.937 KB 4 rows
. . exported "SCOTT"."EMP" 8.570 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.867 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA
**************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01
/home/oracle/Desktop/test/
Job "SCOTT"."SYS_EXPORT_SCHEMA
[oracle@new dbs]$ impdp moon/light directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Import: Release 11.2.0.1.0 - Production on Thu Jan 3 19:34:32 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, 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 "MOON"."SYS_IMPORT_FULL_01
Starting "MOON"."SYS_IMPORT_FULL_01
Job "MOON"."SYS_IMPORT_FULL_01
[oracle@new dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 19:34:48 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> conn moon/light
Connected.
SQL> select * from tab;
no rows selected
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@new dbs]$ export ORACLE_SID=data
[oracle@new dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 19:35:54 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
DATA
SQL> conn moon/light
Connected.
SQL> select * from tab;
no rows selected
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@new dbs]$ impdp moon/light directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Import: Release 11.2.0.1.0 - Production on Thu Jan 3 19:38:17 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, 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 "MOON"."SYS_IMPORT_FULL_01
Starting "MOON"."SYS_IMPORT_FULL_01
Job "MOON"."SYS_IMPORT_FULL_01
[oracle@new dbs]$
Now I want to import to moon schema..
But it is not working fine...
[oracle@new dbs]$
[oracle@new dbs]$ expdp scott/scott directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Export: Release 11.2.0.1.0 - Production on Thu Jan 3 19:09:08 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/P
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/
Processing object type SCHEMA_EXPORT/TABLE/CONSTR
Processing object type SCHEMA_EXPORT/TABLE/INDEX/
Processing object type SCHEMA_EXPORT/TABLE/COMMEN
Processing object type SCHEMA_EXPORT/TABLE/CONSTR
. . exported "SCOTT"."DEPT" 5.937 KB 4 rows
. . exported "SCOTT"."EMP" 8.570 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.867 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA
**************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01
/home/oracle/Desktop/test/
Job "SCOTT"."SYS_EXPORT_SCHEMA
[oracle@new dbs]$ impdp moon/light directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Import: Release 11.2.0.1.0 - Production on Thu Jan 3 19:34:32 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, 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 "MOON"."SYS_IMPORT_FULL_01
Starting "MOON"."SYS_IMPORT_FULL_01
Job "MOON"."SYS_IMPORT_FULL_01
[oracle@new dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 19:34:48 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> conn moon/light
Connected.
SQL> select * from tab;
no rows selected
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@new dbs]$ export ORACLE_SID=data
[oracle@new dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 19:35:54 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
DATA
SQL> conn moon/light
Connected.
SQL> select * from tab;
no rows selected
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@new dbs]$ impdp moon/light directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Import: Release 11.2.0.1.0 - Production on Thu Jan 3 19:38:17 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, 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 "MOON"."SYS_IMPORT_FULL_01
Starting "MOON"."SYS_IMPORT_FULL_01
Job "MOON"."SYS_IMPORT_FULL_01
[oracle@new dbs]$
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Working fine..
[oracle@new dbs]$ impdp scott/scott REMAP_SCHEMA=SCOTT:MOON directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Import: Release 11.2.0.1.0 - Production on Fri Jan 4 02:01:20 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remappings.
[oracle@new dbs]$ impdp moon/light REMAP_SCHEMA=SCOTT:MOON directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Import: Release 11.2.0.1.0 - Production on Fri Jan 4 02:01:47 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MOON"."SYS_IMPORT_FULL_01
Starting "MOON"."SYS_IMPORT_FULL_01
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/P
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_
. . imported "MOON"."DEPT" 5.937 KB 4 rows
. . imported "MOON"."EMP" 8.570 KB 14 rows
. . imported "MOON"."SALGRADE" 5.867 KB 5 rows
. . imported "MOON"."BONUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/
Processing object type SCHEMA_EXPORT/TABLE/CONSTR
Processing object type SCHEMA_EXPORT/TABLE/CONSTR
Job "MOON"."SYS_IMPORT_FULL_01
[oracle@new dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 4 02:02:03 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user
USER is "SYS"
SQL> conn moon/light
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
--------------------------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL>
Thank You
[oracle@new dbs]$ impdp scott/scott REMAP_SCHEMA=SCOTT:MOON directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Import: Release 11.2.0.1.0 - Production on Fri Jan 4 02:01:20 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remappings.
[oracle@new dbs]$ impdp moon/light REMAP_SCHEMA=SCOTT:MOON directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Import: Release 11.2.0.1.0 - Production on Fri Jan 4 02:01:47 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MOON"."SYS_IMPORT_FULL_01
Starting "MOON"."SYS_IMPORT_FULL_01
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/P
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_
. . imported "MOON"."DEPT" 5.937 KB 4 rows
. . imported "MOON"."EMP" 8.570 KB 14 rows
. . imported "MOON"."SALGRADE" 5.867 KB 5 rows
. . imported "MOON"."BONUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/
Processing object type SCHEMA_EXPORT/TABLE/CONSTR
Processing object type SCHEMA_EXPORT/TABLE/CONSTR
Job "MOON"."SYS_IMPORT_FULL_01
[oracle@new dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 4 02:02:03 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user
USER is "SYS"
SQL> conn moon/light
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
--------------------------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL>
Thank You
ASKER
Thank YOu..
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
https://forums.oracle.com/forums/thread.jspa?threadID=2314328