dbawiki

Not enough disk space for the “expdat.dmp” file? Here is a useful 'how-to' to demonstrate the use of pipes:

  - !/bin/ksh
  -  Export with flags (full, consistent) to a compressed file that
  -  is named by SID-month-day using Oracle exp
          - #########################################
  -  Main

  -  Configure environment
. oracle_environment.ksh   # Oracle environment.
export ORACLE_SID=XXX1

DIRECTORY=/apps/home/oracle/local; export DIRECTORY
FILENAME=${DIRECTORY}/Exports/${ORACLE_SID}-`date +%d`.dmp.gz; export FILENAME
LOG=${DIRECTORY}/Exports/${ORACLE_SID}-`date +%d`.log; export LOG
PIPE=${DIRECTORY}/export_pipe.dmp; export PIPE

test -p ${PIPE} || mknod ${PIPE} p
chmod +rw ${PIPE}

cat $PIPE | /usr/local/bin/gzip > ${FILENAME} &
${ORACLE_HOME}/bin/exp log=${LOG} buffer=10485760 file=${PIPE} consistent=yes full=yes << EOF
system/password
EOF
          - ########################################################


  - !/bin/ksh
  -  Oracle imp import for gzipped Oracle exp file.
          - #########################################
  -  Main

  -  Path of exp file.
FILENAME=$1; export FILENAME

PIPE=export_pipe.dmp; export PIPE
test -p ${PIPE} || mknod export_pipe.dmp p
chmod +rw ${PIPE}

/usr/local/bin/zcat ${FILENAME} > ${PIPE} &
${ORACLE_HOME}/bin/imp buffer=10485760 file=${PIPE} full=yes log=impzip.log << EOF
system/password
EOF
          - ####################################################

imp_APPS_DPS.ksh

  -  database is version 9 so imp - not Datapump
  -  database is too big to uncompress - so pipe

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
/usr/sbin/mknod import_pipe p
uncompress < /oracle/export/SID/export_SID_FULL_20140506183004.dmp.Z > import_pipe &
imp PARFILE=imp_APPS_DPS.par

imp_APPS_DPS.par

USERID="/"
BUFFER=10000000
FROMUSER=APPS_DPS
TOUSER=APPS_DPS
TABLES=(XFP_TRANSFER_EXPORTED)
FILE=import_pipe
LOG=import_APPS_DPS_XFP_TRANSFER_EXPORTED.log
IGNORE=y

IMP parfile to import a few tables from one user to another

userid="/"
file=/oracle/export/SID/export_SID_20140117184503.dmp
log=/oracle/export/SID/import_tables_20140128.log
rows=Y
grants=N
indexes=N
ignore=N
fromuser=SQUAREHEAD
touser=HOOPLEHEAD
tables=ORD_MAST,SYS_INTF,BXTVAL_TO_HOST_T,ARCH_ORD_MAST
show=N