|
 |
|
Oracle selective
export with conditional where clause
Oracle Database Tips by Donald Burleson |
Question: I need to create a scaled-down version of my
production database for developers. Can I use the export
utility (exp and expdp) to restrict the rows that are dumped?
Answer: Yes, you can add a where clause to your
export syntax to extract a sub-set of your production rows. In
this example, we restrict the export to rows added in the last
month:
exp scott/tiger tables=tab1,
tab2 query="where mymonth > sysdate - 31"
You can generate syntax for all tables in your schema by building
the export syntax with a query against user_tables.
Using the query Parameter to use WHERE Clause During Export
To export part of the data of a specific object, use the query
parameter. In the following
example, use the query parameter to get only one row from the
tbl_two table and exclude table tbl_one:
directory=dir_test
dumpfile=test_tables.dmp
logfile=test_tables.log
exclude=table:"='tbl_one'"
query=test.tbl_two:"where id=1"
[oracle@localhost tmp]$ expdp test/test parfile=/tmp/test.par
. . exported "test"."tbl_two" 4.914 KB
1 rows
To use different restrictions, you can
use more than one query parameter in
the same parameter file.
For more details on Oracle utilities, see the book
Advanced Oracle Utilities: The Definitive Reference. You
can buy it for 30%-off directly from
Rampant TechPress.
|
|
 |
|
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.
|
|
|
|
