Use impdp with non-SYS user

Running impdp with dba is convinient, but can be risky. create a dedicated user for specific impdp tasks would be more secure.

About DATA_PUMP_DIR

it is a directory object in oracle database, created during database initialization by oracle. To show all directories in an oracle database:

select directory_name, directory_path from dba_directories;

if no directory is specified with datapump, then the default directory will be used instead. Make sure the user is able to read or write to this directory.

Running datapump as sysdba without password

There is a bug in 19c specifically 19.3 directly after installation without patching. This bug prevents expdp or impdp to use as sysdba but still asks for password even OS auth is enabled. Bug ID is 29926041. The workaroud for this bug is feed something dummy to the stdin like the following

echo yes | expdp \"/ as sysdba\" dumpfile=foo.dmp schemas=FOO

if no LOGFILE is provided on the command line, the default export.log file is created and overwritten. But if the dumpfile will not be overwritten if an existing dumpfile name is provided.