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

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.




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



dumpfile= WC_CAMP_HIST_FX.dmp

logfile= WC_CAMP_HIST_FX.txt


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 &



. . 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:


Job "SIEBEL"."SYS_EXPORT_TABLE_01" successfully completed at Tue Apr 25 13:50:51 2017 elapsed 0 00:28:09


EXPDP and IMPDP with Query and REMAP_TABLE

Here we have a table called requests under the SAMPLE schema, what we are going to do is export it and then re import changing its name and the tablespace with a filter. 1. Create export par file and export - 2. Create tablespace for reimport 3. Create import par file and import Summary We have exported and imported a table into a different tablespace, but at the same time renamed the tables using the REMAP_TABLE and REMAP_TABLESPACE parameters.