Home » RDBMS Server » Server Utilities » Exp/Imp from 1 Solaris box to another
Exp/Imp from 1 Solaris box to another [message #72612] Thu, 31 July 2003 13:23 Go to next message
Michele
Messages: 77
Registered: December 2000
Member
Hello,

I need to write a cron job on my solaris machine that exports data from an oracle 8i database on another solaris machine on our network and then imports the data into an Oracle 9i database that is sitting on the machine that is launching the cron job.
Can this be done? Where does the export get dumped? On the machine that is running the script or the machine that is being called?

I have a database link that is created between machines but I am not sure if that needs to be in the script.

As of now, I have the below but I am not sure if I am on the right track:

ORACLE_SID=oracle8i
export ORACLE_SID
exp userid=8i/8i file=weeklyLdat.dmp log=weeklyLdatExp.log tables=(8i.COMPANY,8i.COMNUMBR) rows=y
ORACLE_SID=oracle91
export ORACLE_SID
imp userid=9i/9i file=weeklyLdat.dmp fromuser=8i touser=9i tables=(9i.COMPANY,9i.COMNUMBR)
log=weeklyLdatImp.log rows=y

Any help would be appreciated. Thanks
Michele
Re: Exp/Imp from 1 Solaris box to another [message #72613 is a reply to message #72612] Thu, 31 July 2003 16:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
simplest way is to
use the lower version of exp to do the export
and 
the same lower version of imp to do import.

1. create tnsentries in host1 ( where 8i is installed) to connect to host2 (where 9i is installed,let it be 9idb)
2. create tnsentries in host1 to connect to the local   8i database (let it be 8idb)	

-- the dump file is created locally in host1
[b]
exp userid=8i/8i@8idb file=weeklyLdat.dmp log=weeklyLdatExp.log tables=(8i.COMPANY,8i.COMNUMBR) rows=y[/b]
-- use the dumpfile in host1 using imp tool in host1
[b]imp userid=9i/9i@9idb file=weeklyLdat.dmp fromuser=8i touser=9i tables=(9i.COMPANY,9i.COMNUMBR)  log=weeklyLdatImp.log rows=y
[/b]
-- NOTE: before importing into 9idb you may need to truncate the data.else data will get appended.
-- use     sqlplus -s 9i/9i@9idb @truncate.sql
-- where truncate.sql 	contains
--	   truncate table table1;
--         truncate table table2;
--         exit; -- else sqlplus session will not exit.
-- and you must specify ignore=y in your imp
-- so the new script would be
[b]

exp userid=8i/8i@8idb file=weeklyLdat.dmp log=weeklyLdatExp.log tables=(8i.COMPANY,8i.COMNUMBR) rows=y
sqlplus -s 9i/9i@9idb @truncate.sql
imp userid=9i/9i@9idb file=weeklyLdat.dmp fromuser=8i touser=9i tables=(9i.COMPANY,9i.COMNUMBR) ignore=y
[/b]

Re: Exp/Imp from 1 Solaris box to another [message #72614 is a reply to message #72613] Fri, 01 August 2003 07:45 Go to previous message
Michele
Messages: 77
Registered: December 2000
Member
Thank you Mahesh for a very informative response.

Much appreciated. You helped me a great deal.
Michele
Previous Topic: Oracle Instance Hang
Next Topic: Import
Goto Forum:
  


Current Time: Sat Jun 29 13:45:08 CDT 2024