How to Export and Import ( Expdp and Impdp ) Specific Table Partition and Where Condition ( Query ) in Oracle Datapump

Hi,

I will explain How to Export and Import Specific Table Partition and Where Condition in Oracle Datapump in this article.

 

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.

 

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.

 

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

 

But 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 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

 

If your tables are not partitioned, but you want to export only specific data, then you can use the Where Clause in QUERY option.

 

I will export only last 30 days Customer using Membership_time column.

QUERY=SCHEMA_NAME.TABLE_NAME:'"WHERE filtered_colum =>< interval_number"'

 

Last 30 days’ customer can be queried bu using membership_time>sysdate-30 filter.

 

Add the following query option to your export code.

query=MEHMETSALIH.CUSTOMER:'"WHERE membership_time>sysdate-30"'

 

 

Export code should be as follows, it will export only last 30 days’ customers.

 

expdp \"/ as sysdba\" directory=DATAPUMP dumpfile=CUSTOMER%U.dmp logfile=CUSTOMER.log parallel=64 cluster=n exclude=statistics  tables=MEHMETSALIH.CUSTOMER query=MEHMETSALIH.CUSTOMER:'"WHERE membership_time>sysdate-30"'

 

 

 

 

 

 

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

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

 672 views last month,  7 views today

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 mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply