DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, October 5, 2015

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



1 comment:

Sridevi K said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.