Home » RDBMS Server » Server Utilities » issue in export/import via datapump
issue in export/import via datapump [message #567783] Fri, 05 October 2012 05:18 Go to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
hello experts,

i want to exp/imp emp and dept tables of scott schema to HR schema.

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Neetesh>expdp scott/tiger@localdb tables=emp,dept directory=DATA_PUMP_D
IR dumpfile=exp_scott_tbl.dmp

Export: Release 11.2.0.1.0 - Production on Fri Oct 5 15:32:02 2012

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 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=emp,dept director
y=DATA_PUMP_DIR dumpfile=exp_scott_tbl.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  C:\ADMIN\LOCALDB\DPDUMP\EXP_SCOTT_TBL.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:34:25


C:\Users\Neetesh>impdp hr/hr@localdb tables=emp directory=DATA_PUMP_DIR  dumpfil
e=exp_scott_tbl.dmp remap_schema=hr:scott

Import: Release 11.2.0.1.0 - Production on Fri Oct 5 15:37:36 2012

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 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39166: Object HR.EMP was not found.

or

C:\Users\Neetesh>impdp hr/hr@localdb tables=emp directory=DATA_PUMP_DIR  dumpfil
e=exp_scott_tbl.dmp remap_schema=scott:hr

Import: Release 11.2.0.1.0 - Production on Fri Oct 5 15:40:48 2012

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 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39166: Object HR.EMP was not found.


or

C:\Users\Neetesh>impdp hr/hr@localdb tables=emp directory=DATA_PUMP_DIR  dumpfil
e=exp_scott_tbl.dmp table_exists_action=replace

Import: Release 11.2.0.1.0 - Production on Fri Oct 5 15:41:29 2012

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 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39166: Object HR.EMP was not found.


C:\Users\Neetesh>


why the import process does not get suceesfully complted?
please tell me.

thanks....
Re: issue in export/import via datapump [message #567785 is a reply to message #567783] Fri, 05 October 2012 05:31 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
tables=scott.emp
Re: issue in export/import via datapump [message #567786 is a reply to message #567783] Fri, 05 October 2012 05:33 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Works fine for me (on 10g, though): creating a table in SCOTT schema:
C:\>sqlplus scott/tiger@ora10

SQL*Plus: Release 10.2.0.3.0 - Production on Pet Lis 5 12:30:56 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table test_expimp(col number);

Table created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Export:
C:\>expdp scott/tiger@ora10 dumpfile=test.dmp directory=ext_dir tables=test_expimp

Export: Release 10.2.0.3.0 - Production on Petak, 05 Listopad, 2012 12:31:23

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@ora10 dumpfile=test.dmp directory=ext_dir ta
bles=test_expimp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TEST_EXPIMP"                           0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\TEST.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 12:31:42


Import:
C:\>impdp mike/lion@ora10 dumpfile=test.dmp directory=ext_dir remap_schema=scott:mike

Import: Release 10.2.0.3.0 - Production on Petak, 05 Listopad, 2012 12:31:56

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "MIKE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "MIKE"."SYS_IMPORT_FULL_01":  mike/********@ora10 dumpfile=test.dmp directory=ext_dir remap
_schema=scott:mike
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MIKE"."TEST_EXPIMP"                            0 KB       0 rows
Job "MIKE"."SYS_IMPORT_FULL_01" successfully completed at 12:31:57


C:\>sqlplus mike/lion@ora10

SQL*Plus: Release 10.2.0.3.0 - Production on Pet Lis 5 12:33:23 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc test_expimp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL                                                NUMBER

SQL>
Re: issue in export/import via datapump [message #567793 is a reply to message #567786] Fri, 05 October 2012 06:47 Go to previous message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks alot to all.
my problem has been resolved.

@Littlefoot
thank you very much sir,
your query will export entire dumpfile ,
but when we include 'tables' keyword then we have to give the owner schema name at the time of import
otherwise it will throws an error.

thanks again.....
Previous Topic: Downgrade issue with 10.2.0.5 database
Next Topic: EXPORT and IMPORT from client machine
Goto Forum:
  


Current Time: Thu Mar 28 18:43:19 CDT 2024