Exporting Made A Little Easier

Even though exporting a several tables in a series may not necessarily be a daunting task, it sure can be a tedious one. Therefore the creation of the following script.

A buddy of mine and myself combined two different pieces of our own code to come up with this nice little export script.

The on-demand export script allows you to create a flat file called ondemand.lst and list within it the tables you want to export in the following manner:

owner.tablename
owner.tablename
etc.

The script will loop through this lst file until all entries have been exported.

As an added value, the table being exported will be gzip’ed in memory before being written to disk.

Here is the code for the ondemand_exp.sh script:
Note: Remember to change the

#!/bin/ksh
. /home/sfulke/.profile

curdt=`date +"%Y-%m-%d_%H:%M"`
clear

################################################################
# MODULE: ondemand_exp.sh (ksh)
# DESCRIPTION: Allows user to export tables by listing them in file . Output is
# compressed in memory via gzip.
# PURPOSE: To increase user options when exporting Oracle tables.
# Example: ./ondemand_exp.sh
#Disclaimer: Use is at your sole risk. All info is provided "as is", without any warranty, # whether express or implied, of its accuracy, completeness.
# CHANGE HISTORY:
# Date Who Change Description
# ------------- --------------------- ----------------------------
# 08-NOV-2004 Tony Bryant (abryan) Initial Creation
# 08-NOV-2004 Samual C. Fulkerson Export code supplied
################################################################

exp_log='’
exp_fil=''
exp_cod=''

filename=$exp_cod/ondemand.lst
read_file_line=`wc -l < $filename`
i=1
while [ "$i" -le "$read_file_line" ]
do
table_name=`cat $filename | head -$i | tail -1`

# remove perviously created named pipe
rm $exp_cod/.ondemand.exp.pipe
# create a named pipe
mknod $exp_cod/.ondemand.exp.pipe p
# read the pipe - output to zip file in the background
gzip < $exp_cod/.ondemand.exp.pipe > $exp_fil/exp_$table_name.$curdt.dmp.gz &
# feed the pipe
command="exp userid= file=$exp_cod/.ondemand.exp.pipe log=$exp_log/exp_$table_name._$curdt.log grants=Y index
es=Y rows=Y constraints=Y direct=Y compress=Y tables=$table_name"
$command
wait
i=`expr $i + 1`
done

exit

You can add a mailx statement to email you upon creation it you like. For exporting a large table list you might even wan to add an error checking piece for grep’ing through the log files and emailing you the results to simplify the task somewhat.