STEP BY STEP PROCEDURE TO EXPORT DATA FROM HIGHER ENVIRONMENT AND IMPORT INTO LOWER ENVIRONMENT

STEP 1: CREATE DIRECTORY FOR SCOTT USER IN SOURCE DB 11.2 VERSION:


SQL> create or replace directory test_dir as ‘/home/oracle/dump’;

Directory created.

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

Grant succeeded.

SQL> conn scott/XXXX

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 IN SOURCE DB 11.2 USING VERSION PARAMETER AND COPY THE DUMPFILES TO TARGET SERVER:


expdp scott/xxxxxxxx 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 2015

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/******** 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:
  /HOME/ORACLE/DUMP/TESTVER.DMP
Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at 16:06:54

cd HOME/ORACLE/DUMP/

scp TESTVER.DMP oracle@servername:/home/oracle/dump

STEP 3: CREATE DIRECTORY FOR SCOTT USER IN TARGET DB 10.2 VERSION AND IMPORT IT:


SQL> create or replace directory test_dir as ‘/home/oracle/dump’;

Directory created.

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

Grant succeeded.

impdp scott/xxxxxxxx directory=test_dir dumpfile=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/******** 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

  • October 5, 2015 | 19 views