Categories
Blog

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

 




















Categories
Blog

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 –

userid="/ as sysdba"
 directory=TTS_EXP
 dumpfile=requests_2015.dmp
 compress=y
 logfile=export_requests_2015.txt
 tables=SAMPLE.requests
$ expdp parfile=exp_requests.par
Export: Release 11.2.0.4.0 - Production on Tue Oct 6 12:02:43 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
 With the Automatic Storage Management option
 Legacy Mode Active due to the following parameters:
 Legacy Mode Parameter: "compress=TRUE" Location: Parameter File, ignored.
 Legacy Mode has set reuse_dumpfiles=true parameter.
 Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA parfile=exp_requests.par reuse_dumpfiles=true
 Estimate in progress using BLOCKS method...
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 Total estimation using BLOCKS method: 2.222 GB
 >>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
 >>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
 >>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
 >>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
 >>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
 >>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
 Processing object type TABLE_EXPORT/TABLE/TABLE
 Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
 Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
 Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
 Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
 Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 >>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
 >>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
 >>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
 >>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
 >>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
 >>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
 . . exported "SAMPLE"."requests" 1.695 GB 2800351 rows
 Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
 ******************************************************************************
 Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
 /drive1/backup/export/requests_2015.dmp
 Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Oct 6 12:05:55 2015 elapsed 0 00:03:08

2. Create tablespace for reimport

SQL> CREATE SMALLFILE TABLESPACE "SAMPLE_ARCHIVE_AS" DATAFILE '+DATA' SIZE 1024M REUSE AUTOEXTEND ON NEXT 250M MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
Tablespace created.

3. Create import par file and import

$ vi imp_requests.par
 userid="/ as sysdba"
 directory=TTS_EXP
 dumpfile=requests_2015.dmp
 logfile=import_requests_2015.txt
 tables=SAMPLE.requests
 indexes=no
 query=SAMPLE.requests:"where created < SYSDATE-36"
 remap_table=SAMPLE.requests:requests_20151006
 remap_tablespace=SAMPLE_01:SAMPLE_ARCHIVE_AS
$ impdp parfile=imp_requests.par
Import: Release 11.2.0.4.0 - Production on Tue Oct 6 12:38:15 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "indexes=FALSE" Location: Parameter File, Replaced with: "exclude=index"
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA parfile=imp_requests.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SAMPLE"."requests_20151006" 1.695 GB 2786738 out of 2800351 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 6 12:41:17 2015 elapsed 0 00:03:01

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.