Re: Migrate Oracle 7 database to Oracle 8

jocave_at_yahoo.com (Justin Cave) wrote in message news:<233b7a65.0210021457.64d20fe8_at_posting.google.com>...
> mfaine_at_knology.net (Mark) wrote in message news:<329dd608.0210020837.14ecff97_at_posting.google.com>...
> > I have a very old database that I have recently received responsibility for
> > migrating to Oracle 8.
>
> It sounds like you're not really migrating a database from Oracle7 to
> Oracle8. You're trying to move the data from one database design
> (that happens to reside on Oracle7) to another database design (that
> happens to reside on Oracle8). Is that correct?
>
> > I have designed a better database (not yet implemented just in design phase)
> > and now I'm wondering what would be the best way to migrate the old data
> > over. I'm looking for the fastest and simplest approach. I understand my
> > options to be a SQL script, a JDBC program, a SQLJ program or the
> > equivalent in some other programming language. I'm sure there must also be
> > other ways that I'm not aware of yet.
>
> So you want to accomplish two separate things. You want the data to
> be moved to the new database and you want the old tables to be merged
> into the new tables. Moving the data from one database to another is
> pretty simple. With exp and imp to export the tables from the Oracle7
> and bring them intact into the Oracle8 database. That would leave you
> with perfect copies of the old tables in the new database.
>
> Migrating the data from the old tables to the new tables will be the
> hard part, since you'll have to write lots of logic yourself.
> Straight SQL is probably the fastest method, particularly if we're
> talking a lot of data. I'd tend to recommend using whatever
> tools/languages you're familiar with, since you're going to be
> spending lots more time writing the logic than you will running it.
>
> Justin Cave

I tend to agree with Justin, however, I wouldn't even bother with the Export/Import.

Just create the tables in a new Oracle8i database and create flat files using the SPOOL command in SQL*Plus. Then use SQL*Loader to load them in the new DB.

Or, if the flat files get too large, create a DATABASE LINK between the 2 instances and issue 'INSERT INTO new_table SELECT (whatever) from old_table_at_db_link' commands. Heck, for that matter, you create and populate the new tables with the CREATE AS SELECT syntax.

HTH Stuart Cowen Received on Thu Oct 03 2002 - 19:51:11 CEST