Export and Import of Partition

In this post we will see export and import operation of a table partition.

Let’s see step by step.

Step 1 : Create a directory 

SQL> conn jagruti/oracle
Connected.
SQL> SHOW USER
USER is "JAGRUTI"
SQL> CREATE DIRECTORY DIR_PART AS '/u01';

Directory created.

Step 2 : Run expdp command to take partition backup

[oracle@prod-18c u01]$ expdp jagruti/oracle directory=DIR_PART dumpfile=expdp_partition.dmp logfile=expdp_partiiton.log tables=TRANSACTIONS_MAIN:Y_2015

Export: Release 12.2.0.1.0 - Production on Fri Aug 2 00:35:53 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "JAGRUTI"."SYS_EXPORT_TABLE_01":  jagruti/******** directory=DIR_PART dumpfile=expdp_partition.dmp logfile=expdp_partiiton.log tables=TRANSACTIONS_MAIN:Y_2015 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "JAGRUTI"."TRANSACTIONS_MAIN":"Y_2015"      71.63 KB    2222 rows
Master table "JAGRUTI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JAGRUTI.SYS_EXPORT_TABLE_01 is:
  /u01/expdp_partition.dmp
Job "JAGRUTI"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 2 00:36:55 2019 elapsed 0 00:00:54

[oracle@prod-18c u01]$ 

We can see in above output I have defined tables=TRANSACTIONS_MAIN:Y_2015 clause which will export Y_2015 partition of TRANSACTIONS_MAIN table.

We can see here total 2222 records exported for Y_2015 partition.

Now in next step I will truncate this partition and import the partition using above taken backup.

Step 3 : Truncate Partition 

SQL> SELECT COUNT(*) FROM TRANSACTIONS_MAIN PARTITION(Y_2015);

  COUNT(*)
----------
      2222
SQL> ALTER TABLE TRANSACTIONS_MAIN TRUNCATE PARTITION Y_2015;

Table truncated.

SQL> SELECT COUNT(*) FROM TRANSACTIONS_MAIN PARTITION(Y_2015);

  COUNT(*)
----------
	 0

SQL> 

Step 4 : Import partition

[oracle@prod-18c u01]$ impdp jagruti/oracle directory=DIR_PART dumpfile=expdp_partition.dmp logfile=impdp_partiiton.log tables=TRANSACTIONS_MAIN:Y_2015 TABLE_EXISTS_ACTION=APPEND

Import: Release 12.2.0.1.0 - Production on Fri Aug 2 00:46:28 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "JAGRUTI"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "JAGRUTI"."SYS_IMPORT_TABLE_01":  jagruti/******** directory=DIR_PART dumpfile=expdp_partition.dmp logfile=impdp_partiiton.log tables=TRANSACTIONS_MAIN:Y_2015 TABLE_EXISTS_ACTION=APPEND 
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "JAGRUTI"."TRANSACTIONS_MAIN" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "JAGRUTI"."TRANSACTIONS_MAIN":"Y_2015"      71.63 KB    2222 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "JAGRUTI"."SYS_IMPORT_TABLE_01" successfully completed at Fri Aug 2 00:46:43 2019 elapsed 0 00:00:13

We can see here partition is successfully imported.

Step 5 : Let’s check in table that partition is successfully imported and rows are inserted.

SQL> SELECT COUNT(*) FROM TRANSACTIONS_MAIN PARTITION(Y_2015);

  COUNT(*)
----------
      2222

SQL> 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp