Monday, December 6, 2010

Oracle Import Data Pump common issues ORA-39002: invalid operation , ORA-39001: invalid argument value

Import Data pump and Export Data pump utilitities when used ommonly give following errors.

ORA-39002: invalid operation + ORA-39070: Unable to open the log file. + ORA-29283: invalid file operation + ORA-06512: at "SYS.UTL_FILE", line 475 + ORA-29283: invalid file operation


ORA-39001: invalid argument value + ORA-39000: bad dump file specification + ORA-31640: unable to open dump file "c:\export\GeniePid2_20100828.dmp" for read + ORA-27041: unable to open file

Both of these issue came up when following import dump command was fired
impdp sys/password@tns directory=my_dir dumpfile=GeniePid2_AJ1.dmp remap_schema=GeniePid2:Pid2AJ1 logfile=GeniePid2_AJ1.log TRANSFORM=oid:n

Solution
  1. Create a directory or folder by any name like c:\export in Server Machine (Not Client machine).Please make sure this path must reside where Oracle server is running ; nobody told this to me and I kept banging my head for 2 hours.
  2. Create a directory which is logical or use by oracle for doing export or import grant the users dba role or exp_full_database or imp_full_database as per your usage
          SQL>create directory my_dir as ‘c:\export\';

          SQL> grant read,write on directory my_dir to public;

Both these points resolved my issue.

1 comment: