Monday, September 10, 2012

Oracle 11g new features: DATAPUMP - Partitition


Oracle 11g has several new features. Today I will stay on DataPump technologies 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
http://www.oracle.com/technetwork/database/datapump11g2009-featureover-134227.pdf 

One of the main and essential feature is Partition option. Because if table size more than a little bit Gbytes 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. 

Example:

SQL> conn /as sysdba
Connected.
SQL> create user ulfet identified by ulfet;

User created.

SQL> grant dba to ulfet;   

Grant succeeded.

SQL> conn ulfet/ulfet
Connected.

CREATE TABLE part_table (
  id            NUMBER(10),
  insert_date   DATE,
  obj_id      NUMBER(10),
  data          VARCHAR2(50)
)
PARTITION BY RANGE (insert_date)
(PARTITION part_table_2000 VALUES LESS THAN (TO_DATE('01/01/2001', 'DD/MM/YYYY')),
 PARTITION part_table_2010 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')), 
 PARTITION part_table_2012 VALUES LESS THAN (MAXVALUE));

  
insert into part_table
select object_id id, created insert_date,data_object_id obj_id, owner||'.'||object_name from dba_objects 

commit;

  

SQL> SELECT partitioned
FROM   dba_tables
WHERE  table_name = 'PART_TABLE';

PAR
---
YES

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

PARTITION_NAME
------------------------------
PART_TABLE_2000
PART_TABLE_2010
PART_TABLE_2012

SQL> 



Export entire table including all partitions

[oracle@localhost admin]$ expdp ulfet/ulfet DIRECTORY=DATA_PUMP_DIR DUMPFILE=tables_part.dmp TABLES=ulfet.part_table

Export: Release 11.2.0.1.0 - Production on Mon Sep 10 16:10:43 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ULFET"."SYS_EXPORT_TABLE_01":  ulfet/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=tables_part.dmp TABLES=ulfet.part_table 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4.062 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ULFET"."PART_TABLE":"PART_TABLE_2010"      3.333 MB   71824 rows
. . exported "ULFET"."PART_TABLE":"PART_TABLE_2012"      31.00 KB     632 rows
. . exported "ULFET"."PART_TABLE":"PART_TABLE_2000"          0 KB       0 rows
Master table "ULFET"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ULFET.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/testdb/dpdump/tables_part.dmp
Job "ULFET"."SYS_EXPORT_TABLE_01" successfully completed at 16:11:03




Export specific partition of table:

[oracle@localhost admin]$ expdp ulfet/ulfet DIRECTORY=DATA_PUMP_DIR DUMPFILE=tables_part_new.dmp TABLES=ulfet.part_table:PART_TABLE_2010 

Export: Release 11.2.0.1.0 - Production on Mon Sep 10 16:14:13 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ULFET"."SYS_EXPORT_TABLE_01":  ulfet/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=tables_part_new.dmp TABLES=ulfet.part_table:PART_TABLE_2010 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ULFET"."PART_TABLE":"PART_TABLE_2010"      3.333 MB   71824 rows
Master table "ULFET"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ULFET.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/testdb/dpdump/tables_part_new.dmp
Job "ULFET"."SYS_EXPORT_TABLE_01" successfully completed at 16:14:26

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

Or load data to another schema using remap_schema

[oracle@localhost admin]$ impdp ulfet/ulfet PARTITION_OPTIONS=merge DIRECTORY=DATA_PUMP_DIR DUMPFILE=tables_part.dmp REMAP_SCHEMA=ulfet:sh

Import: Release 11.2.0.1.0 - Production on Mon Sep 10 17:53:36 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ULFET"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ULFET"."SYS_IMPORT_FULL_01":  ulfet/******** PARTITION_OPTIONS=merge DIRECTORY=DATA_PUMP_DIR DUMPFILE=tables_part.dmp REMAP_SCHEMA=ulfet:sh 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SH"."PART_TABLE":"PART_TABLE_2010"         3.333 MB   71824 rows
. . imported "SH"."PART_TABLE":"PART_TABLE_2012"         31.00 KB     632 rows
. . imported "SH"."PART_TABLE":"PART_TABLE_2000"             0 KB       0 rows
Job "ULFET"."SYS_IMPORT_FULL_01" successfully completed at 17:53:57


Let`s check:


SQL> conn sh/sh
Connected.

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

no rows selected

SQL>

There is only single table, not partitioned.

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

2 comments:

  1. Kamran AgayevSeptember 11, 2012 at 4:42 AM

    This is nice feature when you want to load not the whole table with all its partitions, but the specific partition.

    Thanks for sharing Ulfat

    ReplyDelete
  2. UlfetSeptember 11, 2012 at 5:18 AM

    Kamran welcome! Nice to read your comment.

    ReplyDelete

Subscribe to: Post Comments (Atom)

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...