Export from 11g and Import into 10g Database

A common belief is that we can export from Lower version of Oracle Database and import into Higher version Database and not the other way round.

But this is possible as my activity here is documented but only for Datapump not with regular export import.

This procedure is also valid for SCHEMA level and FULL DATABASE.

PRODUCTION : 10.2.0.3.0 (Target)
TEST: 11.1.0.6(Source)

I had to export some tables from Test(11.1.0.6) and import into Production(10.2.0.3.0)

We just need to append the expdp command with "VERSION" e.g

expdp MDORA TABLES=TSTOCK_CATEGDET,TSTOCK_SUBCATEGDET directory=DATA_PUMP_DIR dumpfile=stock.dmp logfile=stock.log version=10.2.0.3


C:\Documents and Settings\dbadmin>expdp MDORA TABLES=TSTOCK_CATEGDET,TSTOCK
_SUBCATEGDET directory=DATA_PUMP_DIR dumpfile=stock.dmp logfile=stock.log
version=10.2.0.3

Export: Release 11.1.0.6.0 - Production on Wednesday, 31 March, 2010 16:07:12

Copyright (c) 2003, 2007, Oracle. All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "MDORA"."SYS_EXPORT_TABLE_01": MDORA/******** TABLES=TSTOCK_CATEGD
ET,TSTOCK_SUBCATEGDET directory=DATA_PUMP_DIR dumpfile=stock.dmp logfile=stock.log version=10.2.0.3
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
. . exported "MDORA"."TSTOCK_CATEGDET" 5.671 KB 6 rows
. . exported "MDORA"."TSTOCK_SUBCATEGDET" 6.421 KB 13 rows
Master table "MDORA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MDORA.SYS_EXPORT_TABLE_01 is:
C:\ORACLE\PRODUCT\10.2.0\ADMIN\TESTDB\DPDUMP\stock.DMP
Job "MDORA"."SYS_EXPORT_TABLE_01" successfully completed at 16:08:02


C:\Documents and Settings\dbadmin>impdp MDORA TABLES=TSTOCK_CATEGDET,TSTOCK
_SUBCATEGDET directory=DATA_PUMP_DIR dumpfile=stock.dmp logfile=stock_imp.
log

Import: Release 10.2.0.3.0 - 64bit Production on Wednesday, 31 March, 2010 16:14
:28

Copyright (c) 2003, 2005, Oracle. All rights reserved.
Password:



Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Master table "MDORA"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MDORA"."SYS_IMPORT_TABLE_01": MDORA/******** TABLES=TSTOCK_CATEGD
ET,TSTOCK_SUBCATEGDET directory=DATA_PUMP_DIR dumpfile=stock.dmp logfile=stock_imp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MDORA"."TSTOCK_CATEGDET" 5.671 KB 6 rows
. . imported "MDORA"."TSTOCK_SUBCATEGDET" 6.421 KB 13 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
Job "MDORA"."SYS_IMPORT_TABLE_01" successfully completed at 16:15:03