Data Pump Expdp Impdp Exclude Include Table Partitions – Table_data option

There are 2 ways to exclude and include table partition to the data pump utility.
1.   Is using data pump APIs. This technique I have explained in this post. Data Pump API
2.   Using exclude table_data option. I have mentioned one example below.
Create a table with partitions to test the expdp.

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2))
     partition by range (HIREDATE)
     (partition p1 values less than (TO_DATE('01-JAN-1981','DD-MON-YYYY')) ,
     partition p2 values less than (TO_DATE('01-JAN-1983','DD-MON-YYYY')) ,
     partition p3 values less than (MAXVALUE)) ;


Table created.

I have inserted some rows to the table.

SQL> ANALYZE TABLE EMP COMPUTE STATISTICS;

Table analyzed.


SQL> SELECT TABLE_NAME, PARTITION_NAME, NUM_ROWS FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='EMP';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
EMP                            P1                                      1
EMP                            P2                                     12
EMP                            P3                                      1

Export only one partitions using exclude option.

$ expdp tables=sthomas.emp exclude=table_data:\"IN \(\'P1\',\'P3\'\)\" job_name=TabParExp dumpfile=exp_TabPar.dmp logfile=exp_TabPar.log directory=exp_dir

Export: Release 11.2.0.3.0 - Production on Mon Nov 11 14:33:49 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."TABPAREXP":  /******** AS SYSDBA tables=sthomas.emp exclude=table_data:"IN ('P1','P3')" job_name=TabParExp dumpfile=exp_TabPar.dmp logfile=exp_TabPar.log directory=exp_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "STHOMAS"."EMP":"P2"                          8.5 KB      12 rows
Master table "SYS"."TABPAREXP" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.TABPAREXP is:
  /export/mqprd/exp_TabPar.dmp
Job "SYS"."TABPAREXP" successfully completed at 14:34:01

Export only one partition using include option.

$ expdp tables=sthomas.emp include=table_data:\"=\'P2\'\" job_name=TabParExp dumpfile=exp_TabPar.dmp logfile=exp_TabPar.log directory=exp_dir

Export: Release 11.2.0.3.0 - Production on Mon Nov 11 14:36:57 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."TABPAREXP":  /******** AS SYSDBA tables=sthomas.emp include=table_data:"='P2'" job_name=TabParExp dumpfile=exp_TabPar.dmp logfile=exp_TabPar.log directory=exp_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
. . exported "STHOMAS"."EMP":"P2"                          8.5 KB      12 rows
Master table "SYS"."TABPAREXP" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.TABPAREXP is:
  /export/mqprd/exp_TabPar.dmp
Job "SYS"."TABPAREXP" successfully completed at 14:37:06


In above examples I have used the command line options for taking the export. You can use the parameter file option also as mentioned below.
Sample content of parfile:
tables=sthomas.emp
exclude=table_data:"IN ('P1','P3')"
job_name=TabParExp
dumpfile=exp_TabPar.dmp
logfile=exp_TabPar.log

directory=exp_dir
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google