Using EXPDP to export table data based on a filter against a different table using ku$

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

 

Posted in Blog Tagged with: , ,

Affiliations

Engagements

  • Oracle OpenWorld 2014
  • Gartner BI Summit 9/3/15-10/3/15
  • UKOUG Business Analytics SIG 12/3/15
  • UKOUG Conference 7/12/15-9/12/15
  • Oracle Ireland Conference 2016 3/3/16-4/3/16
  • UKOUG Systems 2016 12/5/16
  • Oracle OpenWorld 2016
  • POUG 2016 7/10/16
  • DOAG 2016 15/11/16
  • UKOUG 2016 5/12/16

Videos

Posts

  • 2017 (8)
  • 2016 (22)
  • 2015 (22)
  • 2014 (8)
  • 2013 (2)