Home > IT Answers > Storage > Oracle 9i to Oracle 8i Data Transfer
Mayleong
0 pts.
 Oracle 9i to Oracle 8i Data Transfer
Backup & recovery
Hi there I have 2 Oracle DB A) Oracle 9i running on W2K, IPAddr=IP1, SubnetMask = SN1 B) Oracle 8i running on Solaris 2.6, IPAddr = IP2, SubnetMask = SN2 I now need to transfer some data from oracle 8i (solaris) to oracle 9i (win 2k)(the 2 servers being configured with different IP and in different subnet mask, with different OS and different oracle version). What is the best option I could use? Import/Export? SQL*Loader? extract to a file (txt, excel), etc then import???? Also, if I need to transfer the updated data periodically from 9i (win2k) into 8i (solaris), what is the best way to achieve this? Create an auto-job to run a SQL script on my Solaris box? There are some data types not supported in 8i, what do I have to look out for? Any way to detect which are the data types not supported in 8I during the Import function? Basically, I am thinking that if I create the same table structure that is used to store the data in 9i (win2k) as per 8i (solaris), I shld be "safe" as users will be updating the data on 9i (win 2k), but won't be updating the data on 8i (solaris), as my 8i is for data warehousing (inquiry only) But, problem is when I am creating new tables in 9i (win2k), I need to be careful regarding the data types definition, which I can't used on 8i. Can anyone just gave me a list of what are the not supported data type in 8I vs 9I????

Software/Hardware used:
ASKED: June 13, 2005  5:09 AM
UPDATED: June 13, 2005  10:50 AM
RELATED QUESTIONS
Answer Wiki:
To help you set up structures in your v8 instance, take an export of your database, preferably with NOROWS. Download a free DDL extract tool from http://www.ddlwizard.com/dt_download.htm and you will be able to break down and adjust your DDL script(s) until you have a workable (perhaps imperfect) schema into which you can load data. Then you can migrate data via imp/exp or SQL*Net copy etc ... Good luck.
Last Wiki Answer Submitted:  June 13, 2005  8:04 am  by  ColinM   10 pts.
All Answer Wiki Contributors:  ColinM   10 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Are you moving the data from 8i to 9i just once to get things set up, or will this be an ongoing operation? If you are doing it once, I think export/import is the easiest way. The import will create all of the schema objects needed, if they are not already created. If you are going to be transporting data back and forth between instances on a regular basis, your best bet may be to set up a database link between the two, and run ‘insert into tablea select columns from tableb’. As long as both servers are defined in DNS, that should not be a problem. SQLNET 8i is compatible with ORACLE9I and vice-versa.

As for supported datatypes, check the Oracle documentation. 9i may intorduce some new datatypes not used 8i, but I can’t be sure.

Mljsher
 0 pts.