Oracle9i - Transfer Data Between Database Via Expimp

Data transfer between database via exp/imp Scenario : Table zstngrpplay in ECD contains 0 rows while table zstngrpplay in ECQ contains 13 rows. Copy all the zstngrpplay data in ECQ to ECD. Note: schema for ECD is SAPECD and for ECQ is SAPECP.

1) Export data (ECQ) ecibsstg01:oraecq 4> exp system/ecibs2mawi file=stngrp.dmp tables=sapecp.zstngrpplay Export: Release 9.2.0.6.0 - Production on Thu Sep 14 15:27:39 2006 Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.6.0 - Production Export done in WE8DEC character set and UTF8 NCHAR character set About to export specified tables via Conventional Path ... Current user changed to SAPECP . . exporting table ZSTNGRPPLAY 13 rows exported Export terminated successfully without warnings.

2) FTP dmp file to ECD

3) Import data (ECD) ecibsdev01:oraecd 9> imp system/manager file=stngrp.dmp fromuser=sapecp touser=sapecd tables=zstngrpplay ignore=y Import: Release 9.2.0.6.0 - Production on Thu Sep 14 15:37:10 2006 Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.6.0 - Production Export file created by EXPORT:V09.02.00 via conventional path import done in WE8DEC character set and UTF8 NCHAR character set . importing SAPECP's objects into SAPECD . . importing table "ZSTNGRPPLAY" 13 rows imported Import terminated successfully without warnings.

4) Check data in ECD SQL> select * from sapecd.zstngrpplay; CLI STNGR --- ----100 1E001 100 1E002 ..... ..... ..... 100 4E001 100 4T001

STNGRP_TXT ---------------------------------------PJ MS Engineer 001 PJ MS Engineer 002

P.Gudang MS Engineer 001 P.Gudang MS Team 001

13 rows selected.

Scenario : Transfer data from table ZSTF in ECQ where user id started with 900xxxxx to table ZSTF in ECD.

Table ZSTF in ECQ

1) Check the data to be export in ECQ SQL> select pernr 2 from sapecp.zstf 3 where pernr like '900%'; PERNR -------90020081 90020556 90060738 90060760 90060824 90060895 90062003 90091266 90091269 9 rows selected.

2) Export data (ECQ) (with parameter statistics = none) ecibsstg01:oraecq 4> exp system/ecibs2mawi file=zstf.dmp tables=sapecp.zstf query=\"where pernr like \'900%\'\" statistics=none Export: Release 9.2.0.6.0 - Production on Thu Sep 14 17:27:09 2006 Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.6.0 - Production Export done in WE8DEC character set and UTF8 NCHAR character set About to export specified tables via Conventional Path ... Current user changed to SAPECP . . exporting table ZSTF Export terminated successfully without warnings.

9 rows exported

-----------------------------------------------------------------------------(without parameter statistics = none) ecibsstg01:oraecq 1> exp system/ecibs2mawi file=zstf.dmp tables=sapecp.zstf query=\"where pernr like \'900%\'\" Export: Release 9.2.0.6.0 - Production on Thu Sep 14 17:16:39 2006 Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.6.0 - Production Export done in WE8DEC character set and UTF8 NCHAR character set About to export specified tables via Conventional Path ... Current user changed to SAPECP . . exporting table ZSTF EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings.

9 rows exported

3) FTP dmp file to ECD ecibsstg01:oraecq 3> ftp ecibsdev01 Connected to ecibsdev01. 220 ecibsdev01 FTP server (Version 4.1 Tue Jul 6 21:20:07 CDT 2004) ready. Name (ecibsdev01:noel): oraecd 331 Password required for oraecd. Password: 230-Last login: Thu Sep 14 15:35:07 WAUST 2006 on ftp from ecibsstg01.hq.tnb.com.my 230 User oraecd logged in. ftp> ftp> put zstf.dmp 200 PORT command successful. 150 Opening data connection for zstf.dmp. 226 Transfer complete. 8209 bytes sent in 0.1528 seconds (52.48 Kbytes/s) local: zstf.dmp remote: zstf.dmp ftp> ftp> bye 221 Goodbye.

4) Import data (ECD) ecibsdev01:oraecd 3> imp system/manager file=zstf.dmp fromuser=sapecp touser=sapecd tables=zstf ignore=y Import: Release 9.2.0.6.0 - Production on Thu Sep 14 17:33:31 2006 Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.6.0 - Production Export file created by EXPORT:V09.02.00 via conventional path import done in WE8DEC character set and UTF8 NCHAR character set . importing SAPECP's objects into SAPECD . . importing table "ZSTF" 9 rows imported Import terminated successfully without warnings.

5) Check the data imported in ECD SQL> select pernr 2 from sapecd.zstf 3 where pernr like '900%'; PERNR -------90020081 90020556 90060738 90060760 90060824 90060895 90062003 90091266 90091269 9 rows selected.

Table ZSTF in ECD after imp process

Scenario : There are few tables in ECQ using table partition such as ZBILH with its partition ZBILH0180, ZBILH1510, all partition are in reference of TNBD station. The same tables in ECD are not using table partition. Copy data from partition table ZBILH0180 in ECQ to non partition table ZBILH in ECD.

1) Export data (ECQ) ecibsstg01:oraecq 3> exp system/ecibs2mawi file=fizabilh.dmp tables=sapecp.zbilh:zbilh0180 query=\"where nrstation=0180 and nrcust=832199\" statistics=none Export: Release 9.2.0.6.0 - Production on Fri Oct 6 16:33:07 2006 Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.6.0 - Production Export done in WE8DEC character set and UTF8 NCHAR character set About to export specified tables via Conventional Path ... Current user changed to SAPECP . . exporting table ZBILH . . exporting partition ZBILH0180 Export terminated successfully without warnings.

76 rows exported

2) FTP dmp file to ECD ecibsstg01:oraecq 2> ftp ecibsdev01 Connected to ecibsdev01. 220 ecibsdev01 FTP server (Version 4.1 Tue Jul 6 21:20:07 CDT 2004) ready. Name (ecibsdev01:noel): oraecd 331 Password required for oraecd. Password: 230-Last login: Fri Oct 6 12:24:14 WAUST 2006 on ftp from ecibsprd01_per.hq.tnb.com.my 230 User oraecd logged in. ftp> ftp> ftp> put fizabilh.dmp 200 PORT command successful. 150 Opening data connection for fizabilh.dmp. 226 Transfer complete. 123021 bytes sent in 0.1214 seconds (989.2 Kbytes/s) local: fizabilh.dmp remote: fizabilh.dmp ftp> ftp> bye 221 Goodbye.

3) Import data (ECD) imp system/manager file=fizacust.dmp fromuser=sapecp touser=sapecd tables=zcust ignore=y *no further example provided

Additional :

DIRECT = y. For exp to extract data directly bypassing the Evaluating Buffer, fatser than Conventional Path. For EXP and IMP.

Additional :

COMMIT = y. It will commit every transaction imported. This will save up UNDO Tablespace. For IMP only.

Additional :

BUFFER = . Buffer space specified for rows to be put in buffer before writing to disk. The higher BUFFER value the rows could be put in. This will speed up the process. For EXP and IMP.

Additional :

RESUMABLE = y. While importing data into database and user encounter tablespace full or disk full problem, the IMP process would not abort and can be resume after fixing the space problem. Should be use with RESUMABLE_NAME and RESUMABLE_TIMEOUT. For IMP only. RESUMABLE suspend when a space related error is encountered(N) RESUMABLE_NAME text string used to identify resumable statement RESUMABLE_TIMEOUT wait time for RESUMABLE

Additional :

RESUMABLE_NAME = . Text string used to identify resumable statement and will be stated in Oracle alert log, e.g : statement in resumable session 'User SCOTT(54), Session 145, Instance 1' was suspended due to ORA-01653: unable to extend table SCOTT.BIG_TABLE by 1024 in tablespace USERS

Use table DBA_RESUMABLE to monitor all resumable transactions. Additional :

RESUMABLE_TIMEOUT = . Time duration given for solving the tablespace problem before the transaction aborted and will rollback. The default is 7200 sec (2hours).

Example :

imp system/ecibs2mawi file=$1.dmp fromuser=sapecp touser=sapecp tables=$1 ignore=y commit=y buffer=10485760 resumable=y resumable_name=noelah resumable_timeout=43200 statistics=none log=$1_imp.log

This will import the data from *.dmp file with total of 10MB of buffer allocated for this process (to speed up the process) and can be resumable if encounter any tablespace full problem. Time given for solving the problem before aborted is 12 hours.