 |
|
Oracle export with Boolean logic
variables
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.Answer:
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'
and sal<800"
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
spool /tmp/parms.txt
select table_name||',' from user_tables
exit
EOF
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:
root> exp
parfile=mypar.par log=exp.log
if [ `grep EXP- exp.log | wc -l` -gt 0 ] || [ `grep ORA- exp.log | wc
-l` -gt 0 ]; then
mailx -s "Errors in Export" -c larry@oracle.com < exp.log
exit 1
fi
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 ]
then
mailx -s "Failure in /tmp/myexp.log" larry@oracle.com
fi
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
myexport_export_status\=\'N\')\"
In this case, the subquery is causing
this error:
cal_export_tables.sh[7]: 0403-057 Syntax error
at line 7 : `"' is not matched.
The solution is to place the parms in a parfile, and this will
then work:
expdp /
file=$file parfile=myparfile.par
|
|
 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
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
buy it
for 30% off directly from the publisher.
|
|
|
