Skip to content
Advertisements

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.
Advertisements
14 Comments Post a comment
  1. abdul hameed #

    Dear Azar,

    Its very clear we can import a dump from a higher version to lower version. thank you for the useful tips you given.

    I had a problem to upgrade a older version of database into 11gR2.

    Yes my older version of database is 8.0.4.0.0, so there is one and only possiblity to upgrade this db into 11gR2 is Export/Import Utility.

    Can u kindly tell me how to export this db dump and import into 11gR2. i am totally confused with the export utlity version. can u tellme how to identify this dbs Export version and how it will support in 11gR2.

    Hope you understand my Question.

    Expecting your earliest reply

    Regards
    Hamid

    October 21, 2011
    • You can use EXP/IMP utility. you can not user datapump utility for oracle 8, because It was supported from 10g release version. so you just take export from oracle 8 using EXP and then import into oracle 11gr2 using IMP utility…

      October 21, 2011
      • abdul hameed #

        Thank u for your reply, is it possible to import 8.0.4.0.0 Database Dump file into 11gR2 ?

        Is there any Issue we need to expect !!?!??

        Regards
        Hamid

        October 21, 2011
      • no issues If Use EXP/IMP utility…you don’t have choice to use expdp/impdp.

        October 21, 2011
      • abdul hameed #

        Thank you Mr.Azar,

        Very happy to know this.. very soon i will continue here if i face any Issues in Export/Import Part ..

        Thank u again..

        Regards
        Hamid

        October 21, 2011
  2. anand #

    Hi Azar,

    It is nice thing to learn.
    i have problem like client gave me a dump file of higher version than what we have. while client exporting they forgot to
    mention the version parameter
    is it possible to import the dump file to lower version without the version parameter ?

    Thanks
    Anand Eppa

    November 29, 2011
    • Sandeep #

      There is no need of using version parameter while ur doing import from lower version to higher version

      EX : 10g to 11g

      Export from 10g and import to 11g

      July 31, 2015
      • Yes indeed but my post talking about higher to lower

        July 31, 2015
  3. suraj #

    There is a mistake at step 3: the dumpfile name while importing is different.Please look at it carefully.

    March 8, 2013
  4. suraj #

    There is a mistake at step 3: the dump file name while importing is different.Please look at it carefully.

    March 8, 2013
  5. ahmad kazuki #

    Hi Mohamed Azar,

    To import to a lower db version will need to add parameter version. let say if i run export from 11g with parameter version 10.2. Is this copy of export can also be import in 11g?

    November 6, 2016
  6. abdulwajid khan #

    Many Thanks

    January 11, 2017

Trackbacks & Pingbacks

  1. מרכז היבואנים
  2. rapid tuning|ecu remap edinburgh|car chip mods|alnwick garages|engine remapping edinburgh|diesel remap|car tuning

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: