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

How to create a list of tables for export using SELECT statement?

Using the SQL one can create a list of tables to pass as a parameter to export (exp).

set heading off
set pages 0

— create the list for a specific user
SELECT DECODE( rownum, 1, ‘tables=(‘, ‘,’ ), owner || ‘.’ || table_name FROM dba_tables WHERE owner IN ( ‘SCOTT’ )
UNION ALL
SELECT ‘)’, null FROM dual;

Output:
tables=( SCOTT.USERS
, SCOTT.DEPT
, SCOTT.EMP
, SCOTT.BONUS
, SCOTT.SALGRADE
, SCOTT.TEST_OBJECTS
, SCOTT.STATS_BACKUP
, SCOTT.DUMMY1
, SCOTT.BIGEMP
, SCOTT.MYDUAL
)

— in the example below it will create list of tables that begin with F but using the example you can create
SELECT DECODE( rownum, 1, ‘tables=(‘, ‘,’ ), owner || ‘.’ || table_name FROM dba_tables WHERE table_name like ‘F%’
UNION ALL
select ‘)’, null from dual
/

Export/Import to/from a compressed file

To reduce the amount of space used when exporting one can compress the data as creating export file and also import data from a compressed file.

Exporting data directly to a compressed file
#!/bin/ksh

PIPE_FILE=/tmp/exp.pipe
EXP_FILE=exp.dmp.gz

# delete pipe file
rm -f $PIPE_FILE
# create a pipe file
$ mknod $PIPE_FILE p
$ gzip < $PIPE_FILE > $EXP_FILE &
$ exp file=$PIPE_FILE ….

Importing data directly from a compressed file
#!/bin/ksh

rm -f $PIPE_FILE
PIPE_FILE=/tmp/imp.pipe
EXP_FILE=exp.dmp.gz

$ mknod $PIPE_FILE p
$ gunzip < $EXP_FILE > $PIPE_FILE &
$ imp file=$PIPE_FILE ….

If you are using Oracle 10g datapump utility you can’t use the above step as the exdp checks if a file exists before writing and generates an error as the file exists.

How to monitor datapump?

When using datapump one can run the following query to monitor the progress by running the following SQL.

select sid, serial#, sofar, totalwork,
dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;

SID SERIAL# SOFAR TOTALWORK OWNER_NAME STATE JOB_MODE
———- ———- ———- ———- —————————— —————————— ——————————
122 64151 1703 2574 SYSTEM EXECUTING FULL

Setting NLS_LANG for export/import

Last updated: 17-Nov-2008

When exporting/importing one can minimize risk of losing data during import/export by setting NLS_LANG.

– Before starting export set NLS_LANG to be the same character set of the database being exported which means no conversion takes place, all the data will be stored in the export file as it was stored in the database.
– Before starting import set NLS_LANG to be the same value as the it was set during export which means no conversion will take place in the import session, but if the character set of the target database is different the data will automatically be converted when import inserts the data in the database.

OR

– Before starting export set NLS_LANG to be the same character set of the database being imported to which means conversion takes place at this step it will automatically convert during export.
– Before starting import set NLS_LANG to be the same value as the it was set during import which means no conversion will take place as it was already converted during export.

To find the NLS_LANG click on the following link

ORA-19206 when running export (exp)

On Oracle9i if you get the following error when running export (exp) utility, to fix this issue one would need to run the $ORACLE_HOME/rdbms/admin/catmeta.sql as sys to fix this issue.

About to export SYSTEM’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at “SYS.DBMS_XMLGEN”, line 83
ORA-06512: at “SYS.DBMS_METADATA”, line 345
ORA-06512: at “SYS.DBMS_METADATA”, line 410
ORA-06512: at “SYS.DBMS_METADATA”, line 449
ORA-06512: at “SYS.DBMS_METADATA”, line 1156
ORA-06512: at “SYS.DBMS_METADATA”, line 1141
ORA-06512: at line 1