Friday, 19 December 2014

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

Posted by at
Label

15 comments:

  1. Unknown26 October 2015 at 02:26

    Your create table syntex is not correct, you missing " ')) " part after dd-MON-yyyy.


    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 users,
    PARTITION sales_SEP VALUES LESS THAN (TO_DATE('01-SEP-2014','dd-MON-yyyy'))
    TABLESPACE users,
    PARTITION sales_OCT VALUES LESS THAN (TO_DATE('01-OCT-2014','dd-MON-yyyy'))
    TABLESPACE users,
    PARTITION sales_NOV VALUES LESS THAN (TO_DATE('01-NOV-2014','dd-MON-yyyy'))
    TABLESPACE users,
    PARTITION sales_DEV VALUES LESS THAN (TO_DATE('01-DEC-2014','dd-MON-yyyy'))
    TABLESPACE users );

    ReplyDelete
  2. Anonymous20 September 2016 at 06:24

    Good one :-)

    ReplyDelete
  3. .13 October 2016 at 03:26

    Great Job Amit..

    ReplyDelete
  4. Dream's World18 January 2017 at 09:25

    Is it correct your last command ? I mean..is it correct for importing single partition ?

    ReplyDelete
    Replies
    1. Unknown14 February 2017 at 06:42

      no the syntax is different. He imports another dumpfile, meaning the dumpfile with only the partition in it. Which was exported in "2. Export specific partition of table:"

      I didn't test it yet, but it's what I need ...

      Delete
    2. Amit Pawar19 June 2017 at 03:21

      Sorry for late reply..Yes in this scenario we are exporting single partitionso this command is absolutely correct to import single partition being this export dumpfile is having only data for that single partition.

      Delete
  5. Unknown14 February 2017 at 06:41

    This comment has been removed by the author.

    ReplyDelete
  6. Unknown26 October 2017 at 01:55

    hello guys,can you tell me syntax for exporting data from a specific partioned table to a particular date say from aug 2016..?
    plz reply ASAP

    ReplyDelete
  7. Anonymous17 April 2018 at 23:39

    while import we are getting below error for all the sub partitions could you please assist on this..



    ORA-31693: Table data object "ACTLLIAB"."TBLPLANBALANCES_RET":"P_RET"."P_RET_S_2017_09" failed to load/unload and is being skipped due to error:
    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    ORA-39786: Number of columns does not match between export and import databases
    ORA-31693: Table data object "ACTLLIAB"."TBLPLANBALANCES_RET":"P_RET"."P_RET_S_2017_06" failed to load/unload and is being skipped due to error:
    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    ORA-39786: Number of columns does not match between export and import databases
    ORA-31693: Table data object "ACTLLIAB"."TBLPLANBALANCES_RET":"P_RET"."P_RET_S_2017_07" failed to load/unload and is being skipped due to error:
    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error

    ReplyDelete
  8. Piyush Mishra2 May 2018 at 00:24

    Nice

    ReplyDelete
  9. Unknown19 January 2019 at 06:42

    How to import the partition table to normal table?

    ReplyDelete
  10. Learning_Daily11 September 2019 at 15:28

    Thanks for the valuable information!
    only today discovering that when loading tables by partition that the TABLE_EXISTS_ACTION= truncate will truncate entire table (all partitions): I previously thought that the truncate would apply only at the unit level of the partition being loaded. This was a surprise when a single partition failed and I attemted rerun with one partion and emptied the table (only the one partition loaded on the redo had data). Yikes!

    ReplyDelete
  11. Sarat Chand , M.Tech(CSE) 20 January 2021 at 07:18

    good

    ReplyDelete
  12. sairam13 January 2022 at 04:09

    Hi Amit,Many thanks for sharing this wonderful steps..Yesterday we tried takig single partion expdp,while performing expdp its estimating correct size but its taking 0 rows for that partion,tried with another partion also but still same issue,any known issue for this scenario please.db-12.1.0.2

    ReplyDelete

Subscribe to: Post Comments (Atom)