Oracle partition table export and import using datapump

Oracle 11g has several new features. Here we are going to explore few of them related to datapump which was extended and enhanced EXP/IMP which first time was introduced on Oracle 10g.

Here are main features:

• Compression
• Encryption
• Transportable
• Partition Option
• Data Options
• Reuse Dumpfile(s)
• Remap_table
• Remap Data

One of the main and essential feature is Partition option. Because if table size more than a little bit GB and if table is partitioned how to transport this partition tables using EXPDP?

  

You can now export one or more partitions of a table without having to move the entire table.  On import, you can choose to load partitions as is, merge them into a single table, or promote each into a separate table. 

To understand partition feature in expdp-impdp/datapump, let’s create a scenario as below.

We are creating 2 schemas.

1.       User1 – This schema is having partition table SALES which needs to be export

2.       User2 – schema where we are going to import sales table with new name as SALES_T.

1. Create users:

[oracle@NVMBD01PSR183 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 19 13:15:44 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user user1 identified by user1;

User created.

SQL> create user user2 identified by user2;

User created.

SQL> grant connect, resource to user1, user2;

Grant succeeded.

2.Create datapump directory.

SQL>CREATE OR REPLACE DIRECTORY EXPDP_DIR AS ' /oracle/data1/expdp';
SQL> GRANT READ, WRITE ON DIRECTORY EXPDP_DIR to user1,user2;

3.Create partition table SALES.

SQL> conn user1/user1

Connected.

SQL> CREATE TABLE sales

( name       varchar2(5),

time_id       DATE)

PARTITION BY RANGE (time_id)

( PARTITION sales_AUG VALUES LESS THAN (TO_DATE('01-AUG-2014','dd-MON-yyyy

TABLESPACE eodods,

PARTITION sales_SEP VALUES LESS THAN (TO_DATE('01-SEP-2014','dd-MON-yyyy

TABLESPACE eodods,

PARTITION sales_OCT VALUES LESS THAN (TO_DATE('01-OCT-2014','dd-MON-yyyy TABLESPACE eodods,

PARTITION sales_NOV VALUES LESS THAN (TO_DATE('01-NOV-2014','dd-MON-yyyy

TABLESPACE eodods,

PARTITION sales_DEV VALUES LESS THAN (TO_DATE('01-DEC-2014','dd-MON

TABLESPACE eodods );

Table created.

  

SQL> insert into sales values('prod1','01-AUG-2014');

1 row created.

SQL> insert into sales values('prod2','01-SEP-2014');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into sales values('prod3','01-OCT-2014');

1 row created.

Commit;

  

SQL> SELECT partitioned FROM dba_tables WHERE table_name = ‘SALES’;

partitioned

---

YES

Let`s check:

SQL> conn user1/user1

Connected.

SQL> SELECT partition_name FROM user_tab_partitions WHERE table_name = 'SALES';

PARTITION_NAME

------------------------------

SALES_AUG

SALES_DEV

SALES_NOV

SALES_OCT

SALES_SEP

EXPDP:

1. Export entire table including all partitions

[oracle@NVMBD01PSR183 ~]$ expdp user1/user1 directory=EXPDP_DIR dumpfile=sales_table.dmp tables=sales logfile=sales_table.log

Export: Release 11.2.0.3.0 - Production on Fri Dec 19 11:13:37 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "USER1"."SYS_EXPORT_TABLE_01":  user1/******** directory=EXPDP_DIR dumpfile=sales_table.dmp tables=sales logfile=sales_table.log

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 24 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "USER1"."SALES":"SALES_NOV"                5.421 KB       1 rows

. . exported "USER1"."SALES":"SALES_OCT"                5.421 KB       1 rows

. . exported "USER1"."SALES":"SALES_SEP"                5.421 KB       1 rows

. . exported "USER1"."SALES":"SALES_AUG"                    0 KB       0 rows

. . exported "USER1"."SALES":"SALES_DEV"                    0 KB       0 rows

Master table "USER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for USER1.SYS_EXPORT_TABLE_01 is:

  /oracle/data1/expdp/sales_table.dmp

Job "USER1"."SYS_EXPORT_TABLE_01" successfully completed at 11:13:46

2. Export specific partition of table:

[oracle@NVMBD01PSR183 ~]$ expdp user1/user1 directory=EXPDP_DIR dumpfile=sales_table_partition.dmp tables=sales:SALES_NOV logfile=sales_table_partition.log

Export: Release 11.2.0.3.0 - Production on Fri Dec 19 12:31:06 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "USER1"."SYS_EXPORT_TABLE_01":  user1/******** directory=EXPDP_DIR dumpfile=sales_table_partition.dmp tables=sales:SALES_NOV logfile=sales_table_partition.log

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 8 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "USER1"."SALES":"SALES_NOV"                5.421 KB       1 rows

Master table "USER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for USER1.SYS_EXPORT_TABLE_01 is:

  /oracle/data1/expdp/sales_table_partition.dmp

Job "USER1"."SYS_EXPORT_TABLE_01" successfully completed at 12:31:13

IMPDP

Move dmp file to target host (ftp, scp etc)

Or load data to another schema using remap_schema

1. Import entire partition table into new schema with new name.

[oracle@NVMBD01PSR183 ~]$ impdp user2/user2 directory=EXPDP_DIR dumpfile=sales_table.dmp remap_table=sales:sales_t remap_schema=user1:user2 remap_tablespace=user1:user2 table_exists_action = append;

Import: Release 11.2.0.3.0 - Production on Fri Dec 19 11:19:25 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "USER2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "USER2"."SYS_IMPORT_FULL_01":  user2/******** directory=EXPDP_DIR dumpfile=sales_table.dmp remap_table=sales:sales_t remap_schema=user1:user2 remap_tablespace=user1:user2 table_exists_action=append

Processing object type TABLE_EXPORT/TABLE/TABLE

Table "USER2"."SALES_T" 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 "USER2"."SALES_T":"SALES_SEP"               5.421 KB       1 rows

. . imported "USER2"."SALES_T":"SALES_AUG"                   0 KB       0 rows

. . imported "USER2"."SALES_T":"SALES_DEV"                   0 KB       0 rows

. . imported "USER2"."SALES_T":"SALES_OCT"               5.421 KB       1 rows

. . imported "USER2"."SALES_T":"SALES_NOV"               5.421 KB       1 rows

Job "USER2"."SYS_IMPORT_FULL_01" successfully completed at 11:19:30

Let`s check:

SQL> conn user2/user2

Connected.

SQL> select * from sales_t;

NAME  TIME_ID

----- ---------

prod1 01-AUG-14

prod2 01-SEP-14

prod3 01-OCT-14

We have successfully imported entire table with name SALES_T.

2. Now import single partition only.

[oracle@NVMBD01PSR183 ~]$ impdp user2/user2 directory=EXPDP_DIR dumpfile=sales_table_partition.dmp remap_table=sales:sales_t remap_schema=user1:user2 remap_tablespace=user1:user2 table_exists_action = append;

Import: Release 11.2.0.3.0 - Production on Fri Dec 19 13:05:26 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "USER2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "USER2"."SYS_IMPORT_FULL_01":  user2/******** directory=EXPDP_DIR dumpfile=sales_table_partition.dmp remap_table=sales:sales_t remap_schema=user1:user2 remap_tablespace=user1:user2 table_exists_action=append

Processing object type TABLE_EXPORT/TABLE/TABLE

Table "USER2"."SALES_T" 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 "USER2"."SALES_T":"SALES_NOV"               5.421 KB       1 rows

Job "USER2"."SYS_IMPORT_FULL_01" successfully completed at 13:05:28

Here we have successfully imported single partition.

If a partition name is specified, it must be the name of a partition or subpartition in the associated table. 

Only the specified set of tables, partitions, and their dependent objects are unloaded.

  

When you use partition option (PARTITION_OPTIONS) of DataPump you have to select below options:

None - Tables will be imported such that they will look like those on the system on which the export was created.
 

Departition - Partitions will be created as individual tables rather than partitions of a partitioned table.
 

Merge - Combines all partitions into a single table.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!