I recently wanted to drop the indexes during a load and recreate after.
There are several ways to do that, but this time since I had a schema export taken I wanted to extract from the dump file.
Here are the scripts files:
JDEPROD@proa1-/cloudfs/DBA/scripts$ cat expdp_JDE8_schemas.sh export NOW=$(date +"%F_%H%M%S") export ORACLE_SID=JDEPROD export ORAENV_ASK=NO . oraenv echo "ORACLE HOME is: "$ORACLE_HOME mv /cloudfs/DBA/scripts/JDE82_schemas_exp_*.* /cloudfs/DBA/scripts/old_dump $ORACLE_HOME/bin/expdp \'/ as sysdba\' \ schemas=JDE,PRODDTA,PRODCTL,PD920 \ directory=DATA_PUMP_DIR \ CONTENT=METADATA_ONLY \ dumpfile=JDE82_schemas_exp_$NOW.dmp \ logfile=JDE82_schemas_exp_$NOW.log mv /u01/app/oracle/admin/JDEPROD/dpdump/JDE82_schemas_exp_$NOW.* /cloudfs/DBA/scripts
$ cat 2_impdp_index_only.sh export NOW=$(date +"%F_%H%M%S") export ORACLE_SID=JDEPROD export ORAENV_ASK=NO . oraenv echo "ORACLE HOME is: "$ORACLE_HOME $ORACLE_HOME/bin/impdp \'/ as sysdba\' \ schemas=PRODCTL,PRODDTA \ directory=EXP_DIR \ CONTENT=METADATA_ONLY \ INCLUDE="INDEX" \ sqlfile=7_create_indexes.sql \ dumpfile=JDE82_schemas_exp_2017-07-06_122035.dmp \ logfile=JDE82_schemas_exp.log JDEPROD@droa1-/cloudfs/DBA/scripts
Output in the 7_create_indexes.sql file:
$ cat 7_create_indexes.sql | more
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
-- CONNECT PRODDTA
CREATE UNIQUE INDEX "PRODDTA"."F0000194_0" ON "PRODDTA"."F0000194" ("SYEDUS", "SYEDBT", "SYEDTN", "SYEDLN")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE( PCTINCREASE 0)
TABLESPACE "PRODDTAI" PARALLEL 4 ;
ALTER INDEX "PRODDTA"."F0000194_0" NOPARALLEL;
CREATE UNIQUE INDEX "PRODDTA"."F00022_0" ON "PRODDTA"."F00022" ("UKOBNM")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE( PCTINCREASE 0)
TABLESPACE "PRODDTAI" PARALLEL 4 ;
ALTER INDEX "PRODDTA"."F00022_0" NOPARALLEL;
CREATE UNIQUE INDEX "PRODDTA"."F00023_0" ON "PRODDTA"."F00023" ("DNCTID", "DNSY", "DNORNN", "DNKCO", "DNDCT", "DNCTRY", "DNFY", "DNOBNM")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE( PCTINCREASE 0)
TABLESPACE "PRODDTAI" PARALLEL 4 ;
...
..
alexlima.com 