Skip to main content

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.



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

Post a Comment

Popular posts from this blog

Rman encryption and other techniques

Today I will demonstrate Recovery Manager features, especially encryption and some other techniques. First of all let me note about RMAN backup encryption. Staring from Oracle 10g RMAN now creates encrypted backups that cannot be restored by unauthorized people. There are 3 modes of backup encryption: * Transparent encryption * Password encryption * Dual-mode encryption using either transparent or password encryption All RMAN backups are not encrypted but you can encrypt any RMAN backup in the form of a backup set. In this tutorial I will show you how to configure Password encryption. Let`s finish talking and start to demonstrate. check parameters with "SHOW ALL" command, by default encryption is OFF RMAN> show all; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTR...
4 comments
Read more

ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range

Today when I want to change max size of datafile I got strange error. ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' AUTOEXTEND ON NEXT 1280K MAXSIZE 32GB ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range Here is reason: The maximum file size for an autoextendable file has exceeded the maximum number of blocks allowed. After research internet I found that instead of using 32GB we can use 32767M. (same values but in MB). Or you may use 31GB too. ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M;
Post a Comment
Read more

Fix ORA-01139: RESETLOGS option only valid after an incomplete database recovery

While shutting down my TEST database process was hanged. Then I had to use shutdown abort. But when I wanted to start database it did not open. SQL> select name from v$database; NAME --------- TEST SQL> shut abort; ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 6597406720 bytes Fixed Size 2265664 bytes Variable Size 3204451776 bytes Database Buffers 3372220416 bytes Redo Buffers 18468864 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 6552 Session ID: 191 Serial number: 3  What`s wrong?  SQL> alter database open resetlogs; ERROR:    ORA-03114: not connected to ORACLE    SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Pr...
9 comments
Read more