Extract Index DDL from expdp

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 ;

..

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s