a question concerning Oracle's data pump import

241 Views Asked by At

Can we do: impdp dumpfile="a.dmp,b.dmp" where a.dmp and b.dmp are generated with separate expdp invocations?

1

There are 1 best solutions below

0
Littlefoot On

Let's try it.

Export:

Two separate exports: table test

c:\temp>expdp scott/tiger@pdb1 dumpfile=test.dmp directory=ext_dir tables=test

Export: Release 21.0.0.0.0 - Production on Wed Mar 15 20:11:58 2023
Version 21.3.0.0.0

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

Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@pdb1 dumpfile=test.dmp directory=ext_dir tables=test
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TEST"                              6.078 KB      12 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 Wed Mar 15 20:12:57 2023 elapsed 0 00:00:47

Table table1:

c:\temp>expdp scott/tiger@pdb1 dumpfile=table1.dmp directory=ext_dir tables=table1

Export: Release 21.0.0.0.0 - Production on Wed Mar 15 20:13:36 2023
Version 21.3.0.0.0

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

Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@pdb1 dumpfile=table1.dmp directory=ext_dir tables=table1
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "SCOTT"."TABLE1"                            5.054 KB       1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\TABLE1.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Mar 15 20:14:01 2023 elapsed 0 00:00:22


c:\temp>

Import:

Both .dmp files in same impdp:

c:\temp>impdp scott/tiger@pdb1 dumpfile=test.dmp,table1.dmp directory=ext_dir

Import: Release 21.0.0.0.0 - Production on Wed Mar 15 20:17:04 2023
Version 21.3.0.0.0

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

Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39140: dump file "c:\temp\table1.dmp" belongs to job "SCOTT"."SYS_EXPORT_TABLE_01"



c:\temp>

So, no, it won't work. All .dmp files must belong to the same expdp. That's what error explanation says:

Error code: ORA-39140

Description: dump file "string" belongs to job string

Cause: When a dump file set consists of multiple files, all files in the set must be specified for an import operation, and all files must have been produced by the same export job. One of the files provided does not belong to the original dump file set. For instance, it was created by a different export job than the other files.

Action: Remove the dump file indicated in the message and retry the import operation providing only the complete set of dump files created by a specific export job.