Monday, September 26, 2011

VERSION parameter in datapump


VERSION:

With the Export Data Pump parameter VERSION, you can control the version of the dumpfile set, and make the set compatible to be imported into a database with a lower compatibility level. Note that this does not mean that dumpfile set can be used with versions of Oracle Database prior to 10.1.0 because Export and Import Data Pump only work with Oracle Database 10g release 1 (10.1) or later.


The VERSION parameter can be useful in a downgrade situation, for example. Assume you have a 10.2 database and you export with VERSION=9.2. Only database objects and attributes that are compatible with 9.2 will be exported. You could then import the dump file into a 10.1 database whose compatibility is set to 9.2. A 10.1 database set to 9.2 compatibility could theoretically be downgraded to 9.2.

VERSION={COMPATIBLE | LATEST | version_string}

COMPATIBLE = The version of the metadata corresponds to the database compatibility level. Database compatibility must be set to 9.2 or higher. This is the default value.
LATEST = The version of the metadata corresponds to the database release version.
version_string = A specific database version (e.g.: 11.1.0).

If database compatibility is 11.1.* then datapump dumpfile set compatibility is 2.1
If database compatibility is 10.2.* then datapump dumpfile set compatibility is 1.1
If database compatibility is 10.1.* then datapump dumpfile set compatibility is 0.1

Examples:

If you specify a VERSION for an Export Data Pump job that is older than the current database version, certain Export Data Pump features may be unavailable. For example, specifying VERSION=10.1 will cause an error if data compression is also specified for the job (e.g.: COMPRESSION=all)

SQL> host expdp system/sys@delhi  version=10.2 compression=all dumpfile=my.dmp

Export: Release 11.2.0.1.0 - Production on Mon Sep 26 12:28:39 2011

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
ORA-39005: inconsistent arguments
ORA-39055: The COMPRESSION feature is not supported in version 10.2.

Export from Oracle database version:11.2:

expdp hr/hr TABLES=hr.employees2 VERSION=10.2 DIRECTORY=data_pump_dir DUMPFILE=emp2.dmp LOGFILE=emp2.log

Import into oracle database version 10.2:

impdp hr/hr TABLES=hr.employees2 DIRECTORY=data_pump_dir DUMPFILE=emp2.dmp LOGFILE=emp2_imp.log

Enjoy:-)

8 comments:

Oracle DBA said...

same can be found here :

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

Anonymous said...

i have been trying to import into a 10g database but it keeps failing. I think the dump was taken with 11g database.
the error i get is

ORA-39002: invalid operation
ORA-31694: master table ""."SYS_IMPORT_FULL_01" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-39774: parse of metadata stream failed with the following error:
LPX-00007: unexpected end-of-file encountere

raybanoutlet001 said...

replica watches
ralph lauren uk
fitflops sale
michael kors outlet
polo ralph lauren
michael kors outlet
mbt shoes outlet
cheap ray ban sunglasses
oakley sunglasses
michael kors outlet

Unknown said...

yeezy boost 350 v2
lacoste outlet
vapormax
air jordan
nike flyknit
lebron shoes
bape hoodie
converse outlet store
fila shoes
lacoste polo shirts

yanmaneee said...

timberlands
moncler
moncler
off white
vapormax
yeezy boost
kobe sneakers
supreme hoodie
jordan shoes
michael kors outlet

yanmaneee said...

lebron james shoes
supreme outlet
jordan shoes
lebron 10
balenciaga
supreme clothing
michael kors outlet online
yeezy supply
adidas nmd r1
adidas yeezy

Anonymous said...

hermes
goyard bag
yeezy
bape hoodie
yeezy 350
yeezy boost
fear of god
golden goose
yeezy 350
golden goose sale

Anonymous said...

beeijiiu035
golden goose outlet
golden goose outlet
golden goose outlet
golden goose outlet
golden goose outlet
golden goose outlet
golden goose outlet
golden goose outlet
golden goose outlet
golden goose outlet