How to Export ( Expdp ) Specific Table Partition in Oracle Datapump

I will explain How to Export and Import Specific Table Partition and Where Condition in Oracle Datapump in this article. Let’s review the Export ( Expdp ) Specific Table Partition in Datapump

 

You can read the following Datapump export import Tutorial, if you don’t know what is the Datapump and how to export and import a table.

Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-4

 

 

 

 

Export ( Expdp ) Specific Table Partition in Datapump

 

Sometimes export and import operation takes so long because of big tables ( huge amount of data ).

If tables are partitioned, then you can export only related partitions.

 

If you don’t know what is the partitioning and how to partition a table in oracle, you can read the following article.

Partitioning Types ( Range , List, Hash, Interval .. ) in Oracle Database

 

Specific partitions of any table can be exported as follows.

You have Customer table (partitioned by monthly) that have lots of partitions. I will export only last 4 month’s partition export.

 

 

List all partitions of specific table by executing the following script.

SELECT * from (
SELECT PARTITION_NAME,
extractvalue
( dbms_xmlgen.getxmltype
( 'select high_value
from DBA_TAB_PARTITIONS where table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.PARTITION_NAME || ''''),
'//text()' ) as high_value
FROM DBA_TAB_PARTITIONS t
WHERE TABLE_NAME = 'CUSTOMER')
WHERE to_char(add_months(sysdate,-1),'SYYYYMMDD') < high_value;

 

 

Last 4 partitions are as follows.

CUST_201912

CUST_201911

CUST_201910

CUST_201909

 

 

Normally if you export whole table, you should use the following export script. I will export the table in 64 parallel and nocompress

expdp \"/ as sysdba\" directory=DATAPUMP dumpfile=CUSTOMER%U.dmp logfile=CUSTOMER.log parallel=64 cluster=n exclude=statistics  TABLES=MEHMETSALIH.CUSTOMER

 

 

Export ( expdp ) Partition

 

Sometimes tables are very big size and partitioned, if you want to export only some partitions of table, you can export it.

if you want to export only specific partitions, use the following.

TABLES=SCHEMA_NAME.TABLE_NAME:PARTITION_NAME

 

tables=MEHMETSALIH.CUSTOMER:CUST_201912,MEHMETSALIH.CUSTOMER:CUST_201911, MEHMETSALIH.CUSTOMER:CUST_201910, MEHMETSALIH.CUSTOMER:CUST_201909

 

Export Partition script should be as follows.

expdp \"/ as sysdba\" directory=DATAPUMP dumpfile=CUSTOMER%U.dmp logfile=CUSTOMER.log parallel=64 cluster=n exclude=statistics  tables=MEHMETSALIH.CUSTOMER:CUST_201912,MEHMETSALIH.CUSTOMER:CUST_201911, MEHMETSALIH.CUSTOMER:CUST_201910, MEHMETSALIH.CUSTOMER:CUST_201909

 

 

 

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )