1

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.

Hasan Fathi
  • 5,610
  • 4
  • 42
  • 60
user1508682
  • 1,329
  • 5
  • 23
  • 34

1 Answers1

1

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

Kris Rice
  • 3,300
  • 15
  • 33
  • Tried it, now getting this error: ORA-39002: invalid operation ORA-39166: Object SYSTEM.ALL was not found or could not be exported or imported. – user1508682 Nov 24 '19 at 14:31
  • Now, you seem to have a permission issue. Who was the export done as and who was is the import trying to be done as ? – Kris Rice Nov 24 '19 at 14:32
  • Thank you Kris, the user that did the export is not from my DB and I don't know much about it. The user who's doing the import is just the SYSTEM user which i thought has all the permissions, but might be wrong... anything we can do to resolve this? – user1508682 Nov 24 '19 at 14:42
  • You can try the SQLFILE param to get a list of what's in the file. SQLFILE=expfull.sql https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm#sthref354 – Kris Rice Nov 24 '19 at 14:48
  • 1
    I think you need to remove the "TABLES=All" argument from the impdp script above. – ultddave Nov 24 '19 at 19:19
  • Thank you, i will review the file see if it can help us. Thank you very much for your help! – user1508682 Nov 25 '19 at 07:48
  • 1
    Thank you also ultddave, after doing that and also running specific grants to my user i was able to import the DB. – user1508682 Nov 26 '19 at 12:36