[SOLVED] How to import dmp file to oracle DB via SqlDeveloper or CMD in windows?

How to import dmp file to oracle DB via SqlDeveloper or CMD in windows?

I have an Oracle dump file that got exported from an unfamiliar database. I need to import it to my Oracle DB with either SqlDeveloper or command line in windows. When using Data Pump Import Wizard in SqlDeveloper I'm getting the below error: ORA-00942: table or view does not exist

When using the CMD I'm getting the below error: ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation

My command line:

impdp USER/password DUMPFILE=c:\folder_name\file_name.dmp TABLES=All LOG=dump_log.log

I tried different variations and each time the same error.

Thank you for your help.

Solution

The command is missing the directory or the files in the proper directory on the db server. The default is DATA_PUMP_DIR which can be found from the DB as follow.

SQL>  SELECT directory_name, directory_path FROM dba_directories
  2   WHERE directory_name='DATA_PUMP_DIR';
DIRECTORY_NAME    DIRECTORY_PATH
_________________ _________________________________________________________________
DATA_PUMP_DIR     /opt/oracle/admin/ORCL/dpdump/8967C87908440D12E053020011AC6F8A

To make a new directory:

CREATE DIRECTORY MY_DIR AS 'c:\folder_name\';

Then add the directory and remove the path from the file parameter.

impdp USER/password directory=MY_DIR  DUMPFILE=file_name.dmp TABLES=All LOG=dump_log.log

ref:

IMPDP > https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL907

Create directory > https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5007.htm#SQLRF01207