Monday, December 10, 2012

Export from Oracle 11g and Import to Oracle 10g

Using EXPDP take dump from higher version and import into lower version. As you know EXPDP utility firstly was introduce in Oracle 10g. This is enhanced EXP/IMP utility (with additional features, example transport tablespace).
I will use Oracle 11g R2 (11.2.0.1.0) and Oracle 10g R2 (10.2.0.4)

Let`s create directory on Oracle 11g database and give read, write permission.

SQL> create directory my_dir as '/u01/app/oracle/admin/ulfetdb/dpdump';

Directory created.

SQL> grant read, write on directory my_dir to ulfet;

Grant succeeded.

SQL>

Now I will connect as ulfet user and will create new table and insert into one record.

SQL> conn ulfet/ulfet
Connected.

SQL> create table test_tbl (id number, name varchar2(10));

Table created.

SQL> insert into test_tbl values(1, 'Omar');

1 row created.

SQL> commit;

Commit complete.

Time to take export of table using DATAPUMP.

[oracle@localhost dpdump]$ expdp ulfet/ulfet@ulfetdb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl version=10.2

Export: Release 11.2.0.1.0 - Production on Mon Dec 10 14:45:54 2012

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 "ULFET"."SYS_EXPORT_TABLE_01":  ulfet/********@ulfetdb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl version=10.2
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 "ULFET"."TEST_TBL"                          5.289 KB       1 rows
Master table "ULFET"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ULFET.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/ulfetdb/dpdump/test_tbl.dmp
Job "ULFET"."SYS_EXPORT_TABLE_01" successfully completed at 14:46:29

[oracle@localhost dpdump]$

--Take dmp file to Oracle 10g`s db server and use same utility DATAPUMP`s IMPDP.

--To be sure directory works properly. I will create same directory on 10g.

SQL> create directory my_dir as '/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/dpdump'; 

Directory created.

SQL> grant read, write on directory my_dir to ulfet;

Grant succeeded.

SQL> 

Now try to imp

[oracle@localhost ~]$ impdp ulfet/ulfet@mydb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl

Import: Release 10.2.0.4.0 - Production on Monday, 10 December, 2012 13:43:54

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ULFET"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ULFET"."SYS_IMPORT_TABLE_01":  ulfet/********@mydb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl 
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'REC_TBS' does not exist
Failing sql is:
CREATE TABLE "ULFET"."TEST_TBL" ("ID" NUMBER, "NAME" VARCHAR2(10)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "REC_TBS" 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "ULFET"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 13:44:07

[oracle@localhost ~]$ 

--There is error. Tablespace is different. To prevent such error use remap_tablespace option.

[oracle@localhost ~]$ impdp ulfet/ulfet@mydb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl remap_tablespace=REC_TBS:USERS

Import: Release 10.2.0.4.0 - Production on Monday, 10 December, 2012 13:46:22

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ULFET"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ULFET"."SYS_IMPORT_TABLE_01":  ulfet/********@mydb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl remap_tablespace=REC_TBS:USERS 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ULFET"."TEST_TBL"                          5.289 KB       1 rows
Job "ULFET"."SYS_IMPORT_TABLE_01" successfully completed at 13:46:24

[oracle@localhost ~]$

--Now, connect as ulfet and check table

SQL> conn ulfet/ulfet
Connected.

SQL> select * from test_tbl;

        ID NAME
---------- ----------
         1 Omar

SQL> 

--Yes, this task completed.

--But what will happen if user omit version=10.2 during export/import. 
--Export will work normal as usual but while import data you will face exception such as ORA-39142.

--Let`s see.

--Oracle 11g

[oracle@localhost dpdump]$ expdp ulfet/ulfet@ulfetdb directory=my_dir dumpfile=test_tbl2.dmp tables=test_tbl

Export: Release 11.2.0.1.0 - Production on Mon Dec 10 15:13:11 2012

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 "ULFET"."SYS_EXPORT_TABLE_01":  ulfet/********@ulfetdb directory=my_dir dumpfile=test_tbl2.dmp tables=test_tbl 
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 "ULFET"."TEST_TBL"                          5.414 KB       1 rows
Master table "ULFET"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ULFET.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/ulfetdb/dpdump/test_tbl2.dmp
Job "ULFET"."SYS_EXPORT_TABLE_01" successfully completed at 15:13:30

--Oracle 10g

[oracle@localhost ~]$ impdp ulfet/ulfet@mydb directory=my_dir dumpfile=test_tbl2.dmp tables=test_tbl remap_tablespace=REC_TBS:USERS

Import: Release 10.2.0.4.0 - Production on Monday, 10 December, 2012 13:53:30

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/dpdump/test_tbl2.dmp"

EXPDP has version options and supply the following Oracle versions:
  • Oracle 10g R1
  • Oracle 10g R2
  • Oracle 11g R1
  • Oracle 11g R2

To read in Azerbaijani : http://www.slideshare.net/ulfettanriverdiyev/oracle-11g-expdp-alib-oracle-10g-import-etmek-15608810
at

1 comment:

  1. Path InfotechJune 4, 2014 at 12:27 AM

    Path Infotech is in the field of oracle training program from past several years.

    For more info : Ocp Certification in Noida

    ReplyDelete

Subscribe to: Post Comments (Atom)

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...