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 )