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.
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.
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.
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=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
2,748 views last month, 2 views today