datapump export / import tables

Scripts use datapump to export and import data on table level.

~~~~~~~~~~~~~~~~~ export data from source db ~~~~~~~~~~~~~~~~
-- exp_init.par to export data with filters on tables (for Golden Gate initial load)
DIRECTORY=EXP_DIR
DUMPFILE=exp_init.dmp
LOGFILE=exp_init.log
PARALLEL=8
EXCLUDE=INDEX
EXCLUDE=CONSTRAINT
EXCLUDE=GRANT
EXCLUDE=TRIGGER
EXCLUDE=SYNONYM
TABLES=BUS_UNIT,BUS_UNIT_COST,CLAIM
QUERY=BUS_UNIT_COST:"WHERE BE_ID <> 250681 and BE_ID <> 251013",
   CLAIM:"WHERE BE_ID <> 250681 and BE_ID <> 251013"

-- Shell script to call the .par file. Use "nohup" to run it.
export ORACLE_SID=SOURCEDB
PWD=`/path/XXXX`
$ORACLE_HOME/bin/expdp userID/$PWD parfile=exp_init.par

~~~~~~~~~~~~~~~~~~~~ import ~~~~~~~~~~~~~~~~~~~~~
-- optional: drop indexes first (and disable triggers if any)
SQL> alter table userid.CLAIM drop PRIMARY KEY drop INDEX;
SQL> drop INDEX userid.clm_INDX1;

-- imp_init.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp_init.dmp
LOGFILE=imp_init.log
PARALLEL=8
TABLE_EXISTS_ACTION=TRUNCATE
TABLES=BUS_UNIT,BUS_UNIT_COST,CLAIM

-- Shell script for importing
export ORACLE_SID=TARGETDB
echo $ORACLE_SID
PWD=`/path/XXXX`
$ORACLE_HOME/bin/impdp userID/${PWD} parfile=imp_init.par

-- after import, re-create indexes, such as
SQL> CREATE UNIQUE INDEX userid.CLM_PK ON userid.CLAIM
  (CLM_ID, BE_ID)
  parallel 4 nologging
  TABLESPACE CLM_INDEX01;
SQL> ALTER TABLE userid.CLAIM ADD (
  CONSTRAINT CLM_PK
  PRIMARY KEY (CLM_ID, BE_ID)
  USING INDEX userid.CLM_PK
  ENABLE VALIDATE);

SQL> grant select on userid.CLAIM to READONLY_RO;
... ... ...