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 STNGRP_TXT
--- ----- ----------------------------------------
100 1E001 PJ MS Engineer 001
100 1E002 PJ MS Engineer 002
.....
.....
.....
100 4E001 P.Gudang MS Engineer 001
100 4T001 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 9 rows exported
Export terminated successfully without warnings.
------------------------------------------------------------------------------
(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 9 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
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 76 rows exported
Export terminated successfully without warnings.
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 = <in_bytes>. 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 = <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 = <in_second>. 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.
