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

7 comments: