Scenario –
We want to export table data based on a certain filter against a different
table ie . We have table WC_CAMP_HIST_FX, we only want to export the data from
this table where the row_wid exists in table W_CAMP_HIST_F and data that is
only 1 years old in the W_CAMP_HIST_F table. In this situation we can use the syntax ‘ku$’ in the query filter, this is a database function so would work in cloud or on-premises.
Solution
1.
First we will create a par file and in the query
syntax we call the parent table by using ku$.
$ vi
WC_CAMP_HIST_FX.par
userid="aaa/bbb"
directory=TTS_EXP
dumpfile= WC_CAMP_HIST_FX.dmp
logfile= WC_CAMP_HIST_FX.txt
tables= WC_CAMP_HIST_FX
query= WC_CAMP_HIST_FX:"where
exists (select 1 from W_CAMP_HIST_F f where ku$.row_wid = f.ROW_WID and
f.created_dt_wid >= 20161001)"
2.
Execute the expdp
$ nohup
expdp parfile= WC_CAMP_HIST_FX.par &
Processing
object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported
"SIEBEL"."WC_CAMP_HIST_FX" 3.163 GB
23611775 rows
Master table
"SIEBEL"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file
set for SIEBEL.SYS_EXPORT_TABLE_01 is:
/backup/export/WC_CAMP_HIST_FX.dmp
Job
"SIEBEL"."SYS_EXPORT_TABLE_01" successfully completed at
Tue Apr 25 13:50:51 2017 elapsed 0 00:28:09