datapump | Oracle Enablement

Archive for the ‘datapump’ Category

How to Exclude Partition of Table while Exporting using Datapump

Posted in datapump, tagged datapump on April 16, 2013| 2 Comments »

Here I’m going to demonstrate excluding one or more table partitions while exporting table using Datapump.

I shown in below screen I have table with 5 partitions, each for the year of my data [Though I don’t have data in this case]. So I refer them as partion 2008, partition 2009 etc for short.

Script 1 shows the contents of the parameter file. Notice a line with “exclude” that I’m excluding two partitions 2011 and 2012. Down below you notice the log of Export dump it only exported other partitions only.

Now look at the Script 2 and notice a difference in “exclude” statement. The difference is one of the partition that I want to exclude is listed in lower case which is partition 2011. Now when I execute Export dump notice that partion 2011 still exported.

I believe Export dump is looking for partitions that match the case , but always DBA/USER_tables (and others) always have the objects names in the upper case.

If you have to perform this kind of export make sure to have them listed in the Upper case. I believe same goes with INCLUDE option.

Script 1

Contents of parameter file

DUMPFILE="test.dmp"
LOGFILE="test.log"
DIRECTORY=FILEOUTDIR
JOB_NAME='TEST_DUMP'
COMPRESSION=all
CONTENT=ALL
PARALLEL=4
reuse_dumpfiles=yes
TABLES=KP_PART_TAB
exclude=TABLE_DATA:"IN ('KP_PART_TAB_2012','KP_PART_TAB_2011')"

——————

c:\>expdp scott@testdb parfile=c:\temp\test.txt

Export: Release 11.2.0.1.0 – Production on Fri Nov 2 09:39:16 2012

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit

Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting “SCOTT”.”TEST_DUMP”: scott/********@testdb parfile=c:\temp\tes

t.txt

Estimate in progress using BLOCKS method…

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 0 KB

. . exported “SCOTT”.”KP_PART_TAB”:”KP_PART_TAB_2008″ 0 KB 0 rows

. . exported “SCOTT”.”KP_PART_TAB”:”KP_PART_TAB_2009″ 0 KB 0 rows

. . exported “SCOTT”.”KP_PART_TAB”:”KP_PART_TAB_2010″ 0 KB 0 rows

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/USER_PREF_STATISTICS

Master table “SCOTT”.”TEST_DUMP” successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.TEST_DUMP is:

/var/opt/gaim/test.dmp

Job “SCOTT”.”TEST_DUMP” successfully completed at 09:39:26

Script 2

Now I changed parameter file so one of the partition name in lower case.

DUMPFILE="test.dmp"
LOGFILE="test.log"
DIRECTORY=FILEOUTDIR
JOB_NAME='TEST_DUMP'
COMPRESSION=all
CONTENT=ALL
PARALLEL=4
reuse_dumpfiles=yes
TABLES=KP_PART_TAB
exclude=TABLE_DATA:"IN ('KP_PART_TAB_2012','kp_part_tab_2011')"