Is it possible to do a datapump import with a different user than did the export

oracleoracle-10g

I have one db dump below is the log file for that dump

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** PARALLEL=1 SCHEMAS=tmsmv DUMPFILE=tmsmv-20120706.dmp LOGFILE=tmsmv-20120706.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 146.1 MB
...........

I want to restore this dump using another user tmsmv using below command:

sql>conn / as sysdba
CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/home/..../';
GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO tmsmv;
GRANT READ, WRITE ON DIRECTORY SYS.DUMP_DIR TO EXP_FULL_DATABASE;
GRANT READ, WRITE ON DIRECTORY SYS.DUMP_DIR TO IMP_FULL_DATABASE;

And then importing with:

impdp tmsmv/***** schemas=TMSMV directory=DUMP_DIR dumpfile=tmsmv-20120706.dmp

While restoring I am facing below error message:

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

Please help me to solve this issue.

Best Answer

It looks like Oracle can't read the .dmp file, because it's under another user's home directory. Oracle has to be able to read it, not the Linux user you're running the impdp command as. (Contrary to my earlier misleading comments!)

Assuming the two users are in different groups, you may need to make the .dmp file world-readable:

chmod o+r /home/..../tmsmv-20120706.dmp

And usually all directories in the path need to be world-readable and world-executable:

chmod o+rx /home/..../

If you wanted the log file to be created, it would be need to be writable too (chmod o+rwx). If it's in a subdirectory of your home directory, you might need to do this to all intermediate levels.

But relaxing permissions on your home directory can be a problem, so you might be better off using a directory somewhere else. Or leave the file where expdp put it, and grant permissions on that DIRECTORY object to tmsmv instead.

Related Question