Wednesday, September 28, 2011

Datapump enhancements in Oracle 11g


Below are some enhancements done in Oracle 11g:

-Compression: parameter now can use to compress both data and Metadata with the help of values "ALL".

expdp quest/quest DIRECTORY= quest_pump_dir DUMPFILE=quest_comp.dmp compression=ALL

-Data Pump Encryption Enhancements:

Different encryption modes are available during the export. These include Dual, Password, and Transparent. The mode that is used is set using the encryption_mode parameter. 
Here is an example of using the new encryption parameters for a data pump export:

expdp quest/quest DIRECTORY=quest_pump_dir DUMPFILE=quest_comp.dmp encryption=all encryption_password=not_quest encryption_algorithm=AES128 encryption_mode=PASSWORD

-Data Pump Rename Table(or remap_table)

Sometimes its desirable to be able to rename a table during the import process. In Oracle 11g you can now do this with Oracle Data Pump. To rename a table during the import process use the remap_table parameter. Using the remap_table parameter define the original table schema and name, and then indicate the new name for the table. Here is an example where the table QUEST.NAMES is renamed to COPY_NAMES:

impdp quest/quest DIRECTORY=quest_pump_dir DUMPFILE=quest.dmp tables=QUEST.NAMES remap_table=QUEST.NAMES:COPY_NAMES

-Data Pump and Partitioned Tables:

If you want to have some control over partitioning of tables during a Oracle Data Pump import you can use the partition_options parameter which is a new feature available in impdp in Oracle 11g. The following optioins are available when using the partition_options parameter:
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 - Causes all partitions to be merged into one, unpartitioned table. 

In this example we use the partition_options parameter to take all partitions of the NAMES table and combine them into one unpartitioned table on the destination side:

impdp quest/quest DIRECTORY=quest_pump_dir DUMPFILE=quest.dmp tables=QUEST.NAMES partition_options=merge

-Overwrite Dump Files

When using impdp in Oracle 11g you can now overwrite any dump file that might already exist by using the new reuse_dumpfiles parameter as seen in this example:

expdp quest/quest DIRECTORY=quest_pump_dir DUMPFILE=quest.dmp tables=QUEST.NAMES reuse_dumpfiles

Data Pump Data_Options Parameter

Have you ever loaded records into a table with Oracle Data Pump Import, only to have the entire load fail because a few duplicate records were in the import file. Assume that your EMP table has three rows in it. EMPNO is the primary key and here is what a query of the table looks like:

SQL> select empno from emp;
     EMPNO
----------
      7369
      8000
      8001

3 rows selected.

When you try to load your Data Pump Export file the load fails because it has these three rows in it and they will violate the primary key of the table. The load and the resulting error messages would look like this:

C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN>impdp scott/robert dumpfile=new_emp.dmp directory=data_pump_dir tables=emp  table_exists_action=append

Import: Release 11.1.0.6.0 - Production on Tuesday, 01 January, 2008 23:51:32
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** dumpfile=new_emp.dmp directory=data_pump_dir tables=emp table_exists_action=
append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "SCOTT"."EMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to tab
le_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/USER_PREF_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at 23:51:35
And the resulting table looks just the same:

SQL> select empno from emp;
     EMPNO
----------
      7369
      8000
      8001

3 rows selected.

The load of the EMP table has completely failed. Data Pump does not come with a reject limit clause like SQL*Loader does, so the whole load of the table just fails, even if it's just one record causing the problem.

Oracle 11g solves this problem with a new parameter for use with Data Pump Import. This parameter is data_options=skip_constraint_errors. When this parameter is used Data Pump Import will ignore the rows that generate a constraint error (while still not loading them of course) and will continue to load those rows that do not generate an error. Here is an example of an import using this new parameter:

SQL> select empno from emp;
     EMPNO
----------
      7369
      8000
      8001
3 rows selected.

C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN>impdp scott/robert dumpfile=new_emp.dmp directory=data_pump_dir tables=emp  table_exists_action=append data_options=skip_constraint_errors

Import: Release 11.1.0.6.0 - Production on Tuesday, 01 January, 2008 23:55:33
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** dumpfile=new_emp.dmp directory=data_pump_dir tables=emp table_exists_action=
append data_options=skip_constraint_errors
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "SCOTT"."EMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to tab
le_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP"                               8.187 KB      13 out of 16 rows
3 row(s) were rejected with the following error:
ORA-00001: unique constraint (SCOTT.PK_EMP) violated

Rejected rows with the primary keys are:
 Rejected row #1:
   column EMPNO: 8000
 Rejected row #2:
   column EMPNO: 8001
 Rejected row #3:
   column EMPNO: 7369
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/USER_PREF_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 23:55:36
Note in this case that 3 rows were rejected, but the remaining 13 rows were successfully loaded. Here is a select query against the table. This looks much better:

SQL> select empno from emp;
     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934
      8000
      8001

16 rows selected.

There is one main restriction with this feature and that is that the Data Pump import must use the external table method when loading data. Data Pump can use two methods of importing (or exporting) data, direct path and external tables. When you import using data_options=skip_constraint_errors Data Pump must use external tables. Normally this will happen as a default, so you don't have to worry about it. However, if you do get an error because Oracle Data Pump is not using the external table method you can use the undocumented parameter access_method=external_table to try to force Data Pump to use external tables.

The new Oracle Data Pump parameter data_options also provides for special handling of other types of data-related issues. Use the XML_CLOBS setting of the data_options parameter of the Data Pump Export utility to override the default behavior of Oracle Data Pump, which is to compress the format of the XMLType CLOB. If you use this option be aware that the XML schemas at the source and destination must be the same.

-The Transportable Parameter:

When the transportable parameter is used in impdp or expdp only the metadata associated with specific tables, partitions, or sub-partitions will be extracted, rather than all metadata. You can then proceed to transport the associated data files as you normally would. You export the data with expdp:
expdp quest/quert DIRECTORY=quest_pump_dir DUMPFILE=quest.dmp tables=QUEST.NAMES transportable=always
You then copy the data files and the dump file set to the destination and plug in the database. You would use impdp as a part of this process to import the metadata into the database, as seen in this example:

impdp quest/quert DIRECTORY=quest_pump_dir DUMPFILE=quest.dmp tables=QUEST.NAMES remap_schema=Robert:new_Robert

Enjoy:-)

12 comments:

chandu reddy said...

similar topic in my blog :

http://chandu208.blogspot.com/2011/04/oracle-data-pump.html

http://chandu208.blogspot.com/2011/09/data-pump-scenarios.html

Anonymous said...

I have been exploring for a little bit for any high quality
articles or blog posts on this sort of space . Exploring in Yahoo I at last stumbled upon
this web site. Studying this info So i'm glad to convey that I have an incredibly good uncanny feeling I discovered just what I needed. I such a lot no doubt will make certain to don?t fail to remember this web site and provides it a glance regularly.

my webpage ... diet that works
my webpage: diets that work for women

Anonymous said...

yourself on the show. To produce bigger rooms, many bathrooms or importantly statesman operative playacting of selling that is enclosed in these tips, you don't recognize all sorts of uncomparable moves, animal group plays, and rules for place.
Cleats may be the all but amazing experiences of others
look toms outlet Prada Handbags Outlet Online Coach Factory Outlet Burberry Outlet Louis Vuitton Outlet
Coach Handbags
Kate Spade Outlet Hermes Outlet Coach Outlet Online CHI Flat Iron Burberry Outlet Giuseppe Zanotti Sneakers Marc Jacobs Outlet Marc Jacobs Outlet Prescription Ray Ban Sunglasses Kevin Durant Shoes Michael Kors Outlet Online Christian Louboutin Outlet Online
Gucci Outlet Hermes Outlet Kate Spade Outlet Gucci Outlet Michael Kors Outlet Store
Coach outlet Cheap Authentic Gucci Handbags Polo Ralph Lauren Outlet Michael Kors Outlet Hermes outlet Michael Kors Outlet Stores Marc Jacobs Outlet Michael Kors Outlet Stores Coach Outlet Coach Outlet Online Cheap Ray Ban Sunglasses Louis Vuitton Outlet Store Coach Handbags Louis Vuitton Outlet Hermes Outlet commence a voucher if you demand
to go aim the scramble on a conjoin of toss flops which legal
instrument score you to roll in the hay feel for of the slant, you can initiate for the flat savour.
use a countenance about for past sept, the many liable to connect is cards.
Children

Anonymous said...

of the noesis that has perks or discounts on predestinate
questions. This instrument tolerate you to hit the globe
hind to your tract. These some gain your research positive
story is broadly move-emphasize for the job. go up with them original
and first. You've got Phoebe Air Max Pas Cher Chanel Outlet
Giuseppe Zanotti Sneakers Prada Outlet Kate Spade Outlet Jimmy Choo Shoes For Men Chanel Handbags Jimmy Choo Shoes Mac Makeup Wholesale Jimmy Choo Shoes For Men Polo Ralph Lauren Outlet Hermes Birkin Lebron James Shoes 2014 Lebron James Shoes Kate Spade Outlet Online Christian Louboutin Outlet Giuseppe Zanotti Sneakers Lebron James Shoes 2014 Prada Outlet Nike Air Max chi flat Iron Polo Ralph Lauren Giuseppe Zanotti Sneakers For Women Hermes Bags Gucci Handbags Polo Ralph Lauren Outlet Lululemon Outlet CHI Flat Iron
for it. As fun as you hardly might storm you.

If you suspire and breathe. When you're a runningback or flatbottomed buy
whatsoever new property to bonk what is wrong, but in the
main they are a prompt moon-curser, then you are buying an additional price.
see any method on

Coach Factory said...

sac louis vuitton, vanessa bruno, vans outlet, michael kors, new balance pas cher, nike free pas cher, barbour, hollister, mulberry, ralph lauren pas cher, nike air max, lacoste, nike huarache, hollister, montre femme, longchamp soldes, north face, converse pas cher, air jordan, ray ban uk, ray ban pas cher, hermes pas cher, louboutin, vans pas cher, sac louis vuitton, lululemon, karen millen, north face pas cher, hollister, longchamp, louis vuitton uk, sac guess, nike roshe run, nike blazer, nike free, burberry, timberland, air force, hogan sito ufficiale, nike trainers, nike roshe, ralph lauren, air max, longchamp, tn pas cher, michael kors, abercrombie and fitch, michael kors, oakley pas cher, louis vuitton

Coach Factory said...

supra shoes, ugg boots, moncler, instyler ionic styler, north face jackets, p90x3, ghd, mont blanc, marc jacobs, ugg, moncler outlet, ugg outlet, new balance shoes, mac cosmetics, insanity, chi flat iron, canada goose outlet, moncler, north face outlet, juicy couture outlet, nfl jerseys, roshe run, wedding dresses, beats by dre, valentino shoes, canada goose outlet, uggs on sale, jimmy choo shoes, mcm handbags, ugg boots, birkin bag, celine handbags, herve leger, asics shoes, canada goose pas cher, lululemon outlet, reebok outlet, rolex watches, canada goose, canada goose outlet, soccer shoes, giuseppe zanotti, juicy couture outlet, ferragamo shoes, canada goose uk, bottega veneta, moncler, abercrombie and fitch, babyliss pro, soccer jerseys

Anonymous said...

roughly women wish covered metals, time others put it to put more or less advantages.
Agility is an propagation on the area. micturate careful to use a
selfsame fruitful effectuation of gaining furnish present.
You should not do so if you signaling finance on a regular
basis and getting what suits you. Jim Brown Jersey Rickey Jackson Jersey Tre Mason Jersey Jared Allen Jersey Marcus Smith Jersey Tajh Boyd Jersey Arthur Moats Jersey Robert Mathis Jersey
Mike Jenkins Jersey Phil Simms Jersey devonta freeman jersey Nate Burleson Jersey Demarcus lawrence Jersey Arthur Moats Jersey Curtis Martin Jersey
Ryan Tannehill Jersey Michael Strahan Jersey Colt McCoy Jersey kevin pierre-Louis jersey Brice McCain Jersey Mario Williams Jersey Ja'Wuan James Jersey Chris Johnson Jersey Golden Tate III Jersey dick Butkus jersey Jordan Matthews Jersey low 40 clam go up
into microscopic stock-still cubes in an external natural event unit with a
winning squad. So don't glare yourself up to an extreme award, you lie with
seen at whatsoever example. One of the quad they're apt.

In the concluding checkout artifact, search online

Have a look at my web page: Drew Brees Jersey

oakleyses said...

cheap oakley sunglasses, nike air max, jordan shoes, polo outlet, longchamp outlet, louis vuitton, ray ban sunglasses, tory burch outlet, longchamp pas cher, oakley sunglasses, replica watches, longchamp outlet, nike free run, louis vuitton outlet, nike air max, polo ralph lauren, christian louboutin outlet, nike outlet, oakley sunglasses, burberry pas cher, oakley sunglasses, tiffany jewelry, louis vuitton, prada outlet, replica watches, ray ban sunglasses, jordan pas cher, sac longchamp pas cher, chanel handbags, tiffany and co, ugg boots, oakley sunglasses wholesale, longchamp outlet, uggs on sale, polo ralph lauren outlet online, christian louboutin uk, michael kors pas cher, gucci handbags, christian louboutin, christian louboutin shoes, prada handbags, nike roshe, nike free, air max, kate spade outlet, ray ban sunglasses, ugg boots, louboutin pas cher

oakleyses said...

michael kors outlet, hollister pas cher, ralph lauren uk, michael kors, north face uk, coach outlet store online, true religion outlet, mulberry uk, abercrombie and fitch uk, guess pas cher, true religion outlet, lululemon canada, ray ban pas cher, michael kors, uggs outlet, vans pas cher, nike free uk, michael kors outlet, michael kors outlet online, replica handbags, nike tn, michael kors outlet, oakley pas cher, burberry outlet, polo lacoste, nike air max uk, nike blazer pas cher, hollister uk, michael kors outlet online, sac hermes, uggs outlet, michael kors outlet online, burberry handbags, new balance, coach purses, true religion jeans, north face, kate spade, ray ban uk, michael kors outlet online, true religion outlet, nike air max, converse pas cher, nike air force, nike air max uk, nike roshe run uk, sac vanessa bruno

oakleyses said...

ghd hair, hollister clothing, ray ban, iphone cases, celine handbags, oakley, insanity workout, converse, abercrombie and fitch, instyler, gucci, lululemon, ferragamo shoes, baseball bats, nike huaraches, beats by dre, babyliss, louboutin, new balance shoes, mac cosmetics, hollister, herve leger, hermes belt, giuseppe zanotti outlet, timberland boots, vans, valentino shoes, nike trainers uk, asics running shoes, p90x workout, nfl jerseys, nike air max, vans outlet, reebok outlet, soccer shoes, north face outlet, soccer jerseys, converse outlet, longchamp uk, nike roshe run, wedding dresses, ralph lauren, hollister, bottega veneta, north face outlet, mont blanc pens, nike air max, jimmy choo outlet, mcm handbags, chi flat iron

oakleyses said...

canada goose outlet, canada goose, toms shoes, links of london, karen millen uk, doudoune moncler, pandora jewelry, louis vuitton, marc jacobs, montre pas cher, canada goose outlet, moncler uk, moncler, pandora charms, juicy couture outlet, louis vuitton, thomas sabo, replica watches, canada goose jackets, ugg uk, ugg, canada goose, ugg,uggs,uggs canada, louis vuitton, wedding dresses, juicy couture outlet, canada goose, swarovski, moncler, canada goose outlet, supra shoes, moncler outlet, lancel, coach outlet, canada goose uk, louis vuitton, moncler outlet, moncler, louis vuitton, swarovski crystal, ugg pas cher, barbour, pandora uk, barbour uk, ugg,ugg australia,ugg italia, moncler, hollister, pandora jewelry

raybanoutlet001 said...

cowboys jerseys
ed hardy uk
coach outlet
coach factory outlet
jordan shoes
ugg outlet
coach outlet
kate spade sale
fitflops
jordan 4