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... Continue Reading →


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 <... Continue Reading →

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... Continue Reading →

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... Continue Reading →

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... Continue Reading →

Create a free website or blog at WordPress.com.

Up ↑