Export Oracle 11g and import it to Oracle 10g


Exported Oracle 11g :'

 SQL> create directory my_dir as '/u01/app/oracle/admin/anardb/dpdump';
Directory created.

 SQL> grant read, write on directory my_dir to anar;
Grant succeeded.

 SQL> conn anar/anar
Connected.

 SQL> create table test_anar (id number, name varchar2(10));
 Table created.

 SQL> insert into test_anar values(1, 'Ziya');
1 row created.

 SQL> commit;
Commit complete.

 [oracle@localhost dpdump]$ expdp anar/anar@anardb directory=my_dir dumpfile=test_anar.dmp tables=test_anar version=10.2
 Export: Release 11.2.0.1.0 - Production on Tue Dec 10 14:45:54 2013
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 "ANAR"."SYS_EXPORT_TABLE_01": anar/********@anardb directory=my_dir dumpfile=test_anar.dmp tables=test_anar 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 "ANAR"."TEST_ANAR" 5.289 KB 1 rows
 Master table "ANAR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ANAR.SYS_EXPORT_TABLE_01 is:
 /u01/app/oracle/admin/anardb/dpdump/test_anar.dmp
Job "ANAR"."SYS_EXPORT_TABLE_01" successfully completed at 14:46:29
[oracle@localhost dpdump]$

Imported to Oracle 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 anar;
Grant succeeded.
SQL>

[oracle@localhost ~]$ impdp anar/anar@mydb directory=my_dir dumpfile=test_anar.dmp tables=test_anar
Import: Release 10.2.0.4.0 - Production on Tuesday, 10 December, 2013 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 "ANAR"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ANAR"."SYS_IMPORT_TABLE_01": anar/********@mydb directory=my_dir dumpfile=test_anar.dmp tables=test_anar
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error: 
ORA-00959: tablespace 'TBS_OC' does not exist 
Failing sql is:
CREATE TABLE "ANAR"."TEST_ANAR" ("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 "TBS_OC"
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "ANAR"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 13:44:07

What happened? L Error received. To solve this problem :


[oracle@localhost ~]$ impdp anar/anar@mydb directory=my_dir dumpfile=test_anar.dmp tables=test_anar remap_tablespace=TBS_OC:USERS
Import: Release 10.2.0.4.0 - Production on Tuesday, 10 December, 2013 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 "ANAR"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ANAR"."SYS_IMPORT_TABLE_01": anar/********@mydb directory=my_dir dumpfile=test_anar.dmp tables=test_anar remap_tablespace=TBS_OC:USERS
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ANAR"."TEST_ANAR" 5.289 KB 1 rows
Job "ANAR"."SYS_IMPORT_TABLE_01" successfully completed at 13:46:24

Then connect to Oracle 10g DB and check test_anar table .

SQL> conn anar/anar
Connected.

SQL> select * from test_anar;
ID NAME
---------- ----------
1 Ziya

SQL>

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: