How to export from oracle 11.2 and import into 10.2 version

How to export from oracle 11.2 and import into 10.2 version

I think many OTN users asked repeately this question How do i export from higher version and import into lower version.I just show here How do we do.

First : Using Datapump from Oralce 11.2 and import into 10.2 version

Source DB 11.2 Version :

Step 1: Create Directory

SQL> create or replace directory test_dir as 'D:/backupnew/dump';

Directory created.

SQL> grant read,write on directory test_dir to scott;

Grant succeeded.

SQL> conn scott/tiger@azardb
Connected.
SQL> create table testversion(version varchar2(20));

Table created.

SQL> insert into testversion values('oralce11gr2');

1 row created.

SQL> commit;

Commit complete.

Step 2: Export Table using Datapump

C:/Users/mazar>expdp scott/tiger@azardb directory=test_dir dumpfile=testver.dmp tables=testversion

Export: Release 11.2.0.1.0 - Production on Sun Jan 23 15:54:13 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
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@azardb directory=test_dir dumpfile=testver.dmp tables=testversion
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 "SCOTT"."TESTVERSION"                       5.031 KB       1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  D:/BACKUPNEW/DUMP/TESTVER.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:54:40

 Now Go to Target DB 10.2 Version

Step 3: Create Directory for Scott  User.

SQL> create or replace directory test_dir as 'd:/newdump';

Directory created.

SQL> grant read,write on directory test_dir to scott;

Grant succeeded.

Step 3: I just Copied this TESTVER.DMP file into target DB 10.2 Directory and import it

D:/oracle/product/10.2.0/db_2/BIN>impdp scott/tiger@ace directory=test_dir dumpf
ile=testversion.dmp tables=testversion

Import: Release 10.2.0.1.0 - 64bit Production on Sunday, 23 January, 2011 16:04:
31

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "d:/newdump/testversion.dmp" may be an original export dump
 file

It showing error, So you need to export Data in source db 11.2 using Version parameter

Step 4:

C:/Users/mazar>expdp scott/tiger@azardb directory=test_dir dumpfile=testver.dmp tables=testversion
 version=10.2 reuse_dumpfiles=yes

Export: Release 11.2.0.1.0 - Production on Sun Jan 23 16:06:47 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
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@azardb directory=test_dir dumpfile=testver.dmp tables=testversion 
version=10.2 reuse_dumpfiles=yes
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 "SCOTT"."TESTVERSION"                       4.968 KB       1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  D:/BACKUPNEW/DUMP/TESTVER.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 16:06:54

Step 5: Again I copied this dump file to target DB 10.2 directory and import it.

D:/oracle/product/10.2.0/db_2/BIN>impdp scott/tiger@ace directory=test_dir dumpf
ile=testver.dmp tables=testversion remap_tablespace=users_tbs:users

Import: Release 10.2.0.1.0 - 64bit Production on Sunday, 23 January, 2011 16:08:
37

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/********@ace directory=test_dir d
umpfile=testver.dmp tables=testversion remap_tablespace=users_tbs:users
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."TESTVERSION"                       4.968 KB       1 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 16:08:39
D:/oracle/product/10.2.0/db_2/BIN>

Now I successfully import it.

Same Like If you want Import oracle 9i version from export taking of 10g version dumpfile, you just use to export 9.2 export client of Source DB 10.2 data and then import it.

Sourcedb : azardb , Version :11.2

Unfortunately I don’t have Oracle 9i software.So i jsut used 11.2 and 10.2

D:/oracle/product/10.2.0/db_2/BIN>exp scott/tiger@azardb file=d:/newdump/testver
sion.dmp tables=test

Export: Release 10.2.0.1.0 - Production on Sun Jan 23 15:10:32 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           TEST          1 rows exported
Export terminated successfully without warnings.

Target DB : ACE Version :10.2

D:/oracle/product/10.2.0/db_2/BIN>set oracle_sid=ace

D:/oracle/product/10.2.0/db_2/BIN>imp scott/tiger@ace file=d:/newdump/testversio
n.dmp tables=test

Import: Release 10.2.0.1.0 - Production on Sun Jan 23 15:11:03 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                         "TEST"          1 rows imported
Import terminated successfully without warnings