[oracle] 11g – import data with impdp

Approach is as follow:

  1. create directory object inside oracle
  2. grant import rights
  3. perform import

step by step

  1. login as system admin into sqlplus:
    $ORACLE_HOME/bin/sqlplus sys/<password> as sysdba
  2. create oracle directory object pointing to folder where dump is located:
    create directory db_dumps  as '/u01/app/oracle/dbs_to_load';
    grant read,write on directory db_dumps to <dbuser>;
  3. grant import rights to db user:
    grant create any directory to <dbuser>;
    grant imp_full_database to <dbuser>;
  4. import dump file:
    $ORACLE_HOME/bin/impdp <dbuser>/<password> schemas=<db_schema_to_export> directory=db_dumps dumpfile=db_dumpfile.dmp logfile=expdp_dbdump.log remap_schema=<old_schema>:<new_schema> remap_tablespace=<old_schema>:<new_schema>

note: replace values between <xxx> with your own values

Importing with data filtering

Data filtering can be added using QUERY=<table_name>:”<where clause>”

When using it, following error kept coming:

ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.

This was solved by simply moving the entire query parameters into an external file rather than command line, and using argument parfile=<path_to_config_file> when importing data with impdp:

with-error:

./bin/impdp remy_impnof/perso1 directory=zzrd_db_dumps file=db_dumpfile.dmp logfile=impdp_dbdump.log remap_schema=remy:remy_impf remap_tablespace=rddbfile:rddbfile_impfilter tables=zzrd_load_test query=remy_impf.zzrd_load_test:"where rd_uid < (100000)"

=> end up with ORA-39035: Data filter SUBQUERY has already been specified. error message

solved:
create a file called datapump_imp.cfg containing query parameters:

schemas=remy
directory=zzrd_db_dumps
file=db_dumpfile.dmp
logfile=impdp_dbdump.log
remap_schema=remy:remy_impf
remap_tablespace=rddbfile:rddbfile_impfilter
query=remy_impf.zzrd_load_test:"where rd_uid < (100000)"

then call cli with this file:

./bin/impdp remy_impnof/perso1 parfile=/home/remy/Desktop/datapump_imp.cfg

=> import successful

source

Post a Comment

Your email is never published nor shared. You're allow to say what you want...