Oracle export with Boolean logic
Oracle Database Tips by Donald Burleson
Question: Does the Oracle export (expdp)
utility allow for "if" statements and variables? I want to export specific
tables based on a variable value.
Oracle data pump export allows a you can
add a where clause to your export syntax with the "query" keyword
to extract a sub-set of your production rows for export. In this example, we restrict the
export to rows that meet the where clause:
exp scott/tiger tables=tab1 query="where
mymonth > sysdate - 31"
exp scott/tiger tables=emp query=\"where
deptno = 20"\
exp scott/tiger query="where job='SALESMAN'
Another technique is to run a SQL*Plus query to build the
"tables=" list. This is just a hack, and you must manually
remove the final comma from the list:
$ORACLE_HOME/bin/sqlplus -s joe/cool<<EOF
select table_name||',' from user_tables
echo "exp rows=y, tables=" > /tmp/parfile.txt
cat /tmp/mypar.txt >> /tmp/parfile.txt
You can also embed your export into a shell
script, where you have a lot more power and flexibility. In this example
we check for any "ORA-" errors in our export:
if [ `grep EXP- exp.log | wc -l` -gt 0 ] || [ `grep ORA- exp.log | wc
-l` -gt 0 ]; then
mailx -s "Errors in Export" -c email@example.com < exp.log
You can also grep for a positive result
and send an e-mail of your export fails:
root> exp parfile=mypar.par > /tmp/myexp.log
grep "successfully without warnings"
/tmp/myexp.log > /dev/null 2>&1
if [ $? != 0 ]
mailx -s "Failure in /tmp/myexp.log" firstname.lastname@example.org
Here is an example of a complex SQL statement that is used in
a Data Pump export:
expdp / file=$file tables=myexport query\=" where myexport_pidm in (select
myexport_pidm from purdue.myexport where myexport_to_extract\=\'Y\' and
In this case, the subquery is causing
cal_export_tables.sh: 0403-057 Syntax error
at line 7 : `"' is not matched.
The solution is to place the parms in a parfile, and this will
Get the Complete
Oracle Utility Information
The landmark book
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.