Use Case
Need to export / import oracle schema from command line (linux shell)
Solution
Great scripts could be found at:
http://noerror.blogspot.com/2005/01/expimp-korn-shell-scripts.html
However, on my environment they could not be executed due to very minor syntax issues which could be result of different versions of shell executable.
My corrected version is listed below (slightly updated, since I have to call exp / imp under hi-privileged account).
Usage is easy:
./export.sh -p [dba_user_password] -f [export_file_name] -s [schema_name1,schema_name2,...]
./import.sh -p [dba_user_password]
-f [export_file_name]
-o [from_schema_name]
-t [to_schema_name]
Also, some useful information could be found at:
http://www.dbaexpert.com/blog/2008/04/comprehensive-shell-script-to-export-the-database-to-devnull/
and
http://dbamac.wordpress.com/2008/08/01/running-sqlplus-and-plsql-commands-from-a-shell-script/
=============== export.sh =================
#!/usr/bin/ksh
# export.sh - Korn Shell script to export (an) Oracle schema(s).
# January 6, 2005
# Author - John Baughman
# Hardcoded values: copy_dir, pipefile, dba
# Jun 23, 2011
# Changed by SK
dba=sys
copy_dir=/home/oracle/dbcopy/
# Check the dump directory exists
if [[ ! -d "${copy_dir}" ]];then
echo
echo "${copy_dir} does not exist !"
echo
exit 1
fi
pipefile=~/backup/backup_pipe
# Check we have created the named pipe file, or else there is no point
# continuing
if [[ ! -p ${pipefile} ]];then
echo
echo "Create the named pipe file ${pipefile} using "
echo " $ mknod ${pipefile} p"
echo
exit 1
fi
# Loop through the command line parameters
# -p - Currently the PSG user password.
# -d - The Oracle SID of the database to export. This matches the TNS entry.
# -s - The schema to import
# If either/all of these don't exist, prompt for them.
# Now, check the parameters...
while getopts ":p:f:s" opt; do
case $opt in
p ) psg_password=$OPTARG ;;
f ) raw_name=$OPTARG ;; #raw_name=${OPTARG##/*/} ;;
s ) shift $(($OPTIND-1))
raw_schema=$* ;;
\? ) print "usage: export.sh -p password -s schema[...]"
exit 1 ;;
esac
done
# Get the missing ${dba} user password
while [[ -z ${psg_password} ]]; do
read psg_password?"Enter ${dba} Password: "
done
# Get the missing SID
#while [[ -z ${sid} ]]; do
#read sid?"Enter Oracle SID: "
#done
# Get the missing "raw" name
while [[ -z ${raw_name} ]]; do
read raw_name?"Enter the export dump file name: "
done
while [[ -z ${raw_schema} ]]; do
print "Enter the schema(s)"
read raw_schema?"(if more than one schema, they can either be space or comma delimited): "
done
# Fix up the raw schema list
schema_list=""
#raw_schema=$(print $raw_schema tr "[a-z]" "[A-Z]")
for name in $raw_schema; do
if [[ -z $schema_list ]]; then
schema_list="${name}"
else
schema_list="${schema_list},${name}"
fi
done
raw_schema=${schema_list}
schema_list="(${schema_list})"
# Fix up the export file name here...
export_file=${copy_dir}${raw_name}.Z
# Let's go!!!
print ""
print "*******************************************************"
print "* Exporting ${raw_schema} schema(s) at `date`"
print "*******************************************************"
print
compress < ${pipefile} > ${export_file} &
exp \'${dba}/${psg_password} as sysdba\' direct=y log=${copy_dir}${raw_name}.exp.log statistics=none buffer=1000000 feedback=10000 file=${pipefile} owner=${schema_list}
#exp ${dba}/${psg_password}@${sid} direct=y log=${copy_dir}${raw_name}.exp.log statistics=none buffer=1000000 feedback=10000 file=${pipefile} owner=${schema_list}
print
print "*******************************************************"
print "* Export Completed at `date`"
print "*******************************************************"
print
exit 0
=============== import.sh =================
#!/usr/bin/ksh
# import.sh - Korn Shell script to import (an) Oracle schema(s).
# January 6, 2005
# Author - John Baughman
# Hardcoded values: copy_dir, pipefile, dba
# Jun 23, 2011
# Changed SK
dba=sys
copy_dir=/home/oracle/dbcopy/
# Check the dump directory exists
if [[ ! -d "${copy_dir}" ]];then
echo
echo "${copy_dir} does not exist !"
echo
exit 1
fi
pipefile=~/backup/backup_pipe2
# Check we have created the named pipe file, or else there is no point
# continuing
if [[ ! -p ${pipefile} ]]; then
echo
echo "Create the named pipe file ${pipefile} using "
echo " $ mknod ${pipefile} p"
echo
exit 1
fi
# Loop through the command line parameters
# -p - Currently the PSG user password.
# -d - The Oracle SID of the database to export. This matches the TNS entry.
# -f - The export file to import.
# If either/all of these don't exist, prompt for them.
# Now, check the parameters...
while getopts ":p:f:o:t" opt; do
case $opt in
p ) psg_password=$OPTARG ;;
f ) raw_name=${OPTARG##/*/} ;;
o ) from_user=$OPTARG ;;
t ) shift $(($OPTIND-1))
to_user=$* ;;
\? ) print "usage: import.sh -p password -f dump_file.dmp -o from_user -t to_user"
exit 1 ;;
esac
done
# Get the missing ${dba} user password
while [[ -z ${psg_password} ]]; do
read psg_password?"Enter ${dba} Password: "
done
# Get the missing SID
#while [[ -z ${sid} ]]; do
#read sid?"Enter Oracle SID: "
#done
# Get the missing "raw" name
while [[ -z ${raw_name} ]]; do
read raw_name?"Enter the export dump file name: "
done
# Get the missing "from_user" name
while [[ -z ${from_user} ]]; do
read from_user?"Enter the from_user name: "
done
# Get the missing "to_user" name
while [[ -z ${to_user} ]]; do
read to_user?"Enter the to_user name: "
done
# Fix up the export file name here...
export_file=${copy_dir}${raw_name}.Z
print
print "*******************************************************"
print "* Importing ${export_file} at `date`"
print "*******************************************************"
print
uncompress < ${export_file} > ${pipefile} & imp \'${dba}/${psg_password} as sysdba\' log=${copy_dir}${raw_name}.imp.log statistics=none buffer=1000000 feedback=10000 file=${pipefile} ignore=y fromuser=${from_user} touser=${to_user}
print
print "*******************************************************"
print "* Import Completed at `date`"
print "*******************************************************"
print
