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 \’/ … Continue reading Extract Index DDL from expdp

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 , … Continue reading How to create a list of tables for export using SELECT statement?

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 … Continue reading Export/Import to/from a compressed file

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 Continue reading How to monitor datapump?

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 … Continue reading Setting NLS_LANG for export/import

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”, … Continue reading ORA-19206 when running export (exp)