Thursday, 2 November 2017

Oracle partition table export and import using data pump



 
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 schema.

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 sub partition 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.
at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)