ORA-39001:ORA-39000:ORA-39142

Error

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file “/bds/app/oracle/product/cedb/10.2.0/admin/cdprod/dpdump/tables236351_01.dmp”

Solution

  • Import 11g data dump into 10g

You can use Oracle data dump to export data dump from 11g server, and import into 10g using the data Pump parameter called Version. When you use Version, Datapump exports all objects that are compatible for your target version, so If your database is 11g and you want to export 11g and import into 10g from 11g db.

$ expdp Test_schema_name/passs directory=datapump schemas=Test_schema_name Version=10.2.0.4.0.

Once the export is done, you do the regular import from 10g server.

  • Import multiple dump files

If the size of the dump file is large, usually they will be split into smaller chunks for easier ftp upload/download.  If you are trying to import a dump file that is split into many chunks, then you need to modify the DUMPFILE paratermeter to include %U

Ex:  If the dump files are named EXP_PROD_1.dmp, EXP_PROD_2.dmp etc, then DUMPFILE=EXP_PROD_%U.DMP

  • How to kill data pump jobs

When you import or export using data pump impdp or expdp tools, the import/export is done by a job. You have an option to provide a job name using JOB_NAME parameter too.  Following sql will give you the list of data pump jobs

select * from dba_datapump_jobs

If you want to kill your impdp or expdp

  1. Make sure that your impdp/expdp command prompt window is active
  2. Press Control-C , It will pause the job. Don’t press another Control-C or close the command prompt. This will just close the window, but the job will still be running in the background.
  3. Type Kill_Job
    ex: Import> kill_job
    Are you sure you wish to stop this job (y/n): y
    If by mistake, you closed the window and your import/export job is still running,
  4. Get the name of the job using
    1. select * from dba_datapump_jobs
  5. Open a new command prompt window. If you want to kill your import job type
    1. impdp username/password@database attach=name_of_the_job
  6. Once you are attached to job, Type Kill_Job

ex: Import> kill_job
Are you sure you wish to stop this job (y/n): y

And your job is killed, it will no longer show in dba_datapump_jobs

7.  REUSE_DUMPFILE 🙁 Overwrite existing dumpfile)

This is the option with data pump expdp utility. Normally when you perform the export using expdp utility and if the dumpfile is present in the export directory it will throw an error “ORA-27038: created file already exists”. This situation happens when you wanted to perform the repetitive exports using the same dumpfile. Oracle provides an option reuse_dumpfile=[Y/N] to avoid this error. You should mention the parameter value as Y to overwrite the existing dump file. By default the option considered as N.

$ expdp scott/tiger directory=exp_dir dumpfile = x.dmp table s= example reuse_dumpfiles = y

For Oracle Apps DBA, Oracle DBA and RAC Training, mail us at training@fusionappsdba.com

Nagulu Polagani

"We are all apprentices in a craft where no one ever becomes a master."