expdp/impdp: export and import between releases using datapump

Hello,

Until 10g when you want to export and import between releases (i.e 10g to 11g), For example you want to export from 11g database and import it into 10g.

This is not possible until 10g, unless you use the higher version binaries(means expdp/impdp executables) in both databases.

But from 11g R1 onwards you have an option to do so using VERSION option.

Thanks to my colleague again (Satish) who asked me this and I have to search for it as I remembered sometime back of this option, hope this will be useful to you all as well.

You can import your 11g expdp dump file into Oracle 10g if you make sure you use "VERSION=10.2" parameter during export.

Here is the example of using the expdp version parameter to make a dump file acceptable to an earlier release of Oracle:

1) Export using the expdp version parameter:

expdp hr/hr TABLES=hr.employees2 VERSION=10.2 DIRECTORY=data_pump_dir DUMPFILE=emp2.dmp LOGFILE=emp2.log
Export: Release 11.2.0.1.0 - Production on Thu Jan 3 20:10:51 2010
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 "HR"."SYS_EXPORT_TABLE_01":  hr/******** TABLES=hr.employees2 VERSION=10.2 
          DIRECTORY=data_pump_dir DUMPFILE=emp2.dmp LOGFILE=emp2.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HR"."EMPLOYEES2"                           16.12 KB     107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:/u01/app/oradata/ORCL/exports/emp2.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 20:11:01

Import into earlier release of Oracle:-

1) Copy the dump file into 10g data_pump_directory and grant permissions to HR

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ----------------------------------------
    DATA_PUMP_DIR                  /u01/app/oradata/10g2/exports
2) SQL> grant read, write on directory data_pump_dir to hr;
Grant succeeded.

SQL> exit

Import the dump file using 10g binaries, observe the version of the Release is 10.2.0.1

3) impdp hr/hr TABLES=hr.employees2 DIRECTORY=data_pump_dir DUMPFILE=emp2.dmp  
Import: Release 10.2.0.1.0 - Production on Thursday, January, 2013 20:15:53
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Master table "HR"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01":  hr/******** TABLES=hr.employees2 DIRECTORY=data_pump_dir 
   DUMPFILE=emp2.dmp LOGFILE=emp2_imp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMPLOYEES2"                           16.12 KB     107 rows
Job "HR"."SYS_IMPORT_TABLE_01" successfully completed at 20:17:05

4) Check the data
sqlplus hr/hr
SQL*Plus: Release 10.2.0.1.0 - Production on Thursday Jan 03 20:20:37 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> select count(*) from employees2;
  COUNT(*)
----------
       107

Nice and cool feature rather struggling with different versions of oracle to achieve the same.

Hope this helps

-Thanks

Geek DBA