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

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

Fix: ORA-13639: The current operation was interrupted because it timed out.

Sometimes SQL Tuning Advisor interrupts cause time limit took more than defined. You have to analyze it or increase value. Here you can see increasing of value. Example result of sql select                  execution_name, advisor_name,                  to_char(execution_start,'dd-mon-yy hh:mi:ss') execution_start,                  to_char(execution_end,'dd-mon-yy hh:mi:ss') execution_end, status,error_message from dba_advisor_executions where task_name = 'SYS_AUTO_SQL_TUNING_TASK' order by execution_start; Check value of TIME_LIMIT`s parameter : SQL> column parameter_value for A35 SQL> select parameter_name, parameter_value from dba_advisor_parameters where task_name = 'SYS_AUTO_SQL_TUNING_TASK' and parameter_name in ('TIME_LIMIT', 'DEFAULT_EXECUTION_TYP...
Post a Comment
Read more

How to fix ORA-26040: Data block was loaded using the NOLOGGING option

Today I faced with new ORA error. After solving I want to share this experience with yours. So, today 5`th datafile of my database was corrupted (/u01/app/oracle/oradata/ulfet_db/example01.dbf). After recover via RMAN I saw strange error. RMAN> recover datafile 5 block 443; Starting recover at 24-MAR-13 using channel ORA_DISK_1 channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00005 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp tag=TAG20130324T223233 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03 starting media recovery media recovery complete, elapsed ti...
2 comments
Read more