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 )

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *