advertisements
_____________________________________________________________________________________________________________________
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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment