Migrating data between 2 databases in Oracle 9i

Question

Hi,

I am new to Oracle. Since we have rewritten an earlier application , we have to migrate the data from the earlier database in Oracle 9i to a new database , also in 9i, with totally different structures. The column names and types would be totally different. We need to map the tables and columns , try to export as much data as possible, eliminate duplicates, and fill empty values with defaults.

Are there any tools which can help in mapping the elements of the 2 databases , with rules to handle duplicates, and default values and migrate the data ?

Thanks,

Chak.

Answer 1

In many cases, creating a database link and some scripts a'la

insert into newtable select distinct foo, bar, 'defaultvalue' from oldtable@olddatabase where xxx

should do the trick

Answer 2

Hi Chakravarthy,

If your goal is to migrate data between two very different schemas you will probably need an ETL solution (ETL=Extract Transform Load).

An ETL will allow you to:

• Select data from your source database(s) [Extract]
• apply business logic to the selected data [Transform] (deal with duplicates, default values, map source tables/columns with destination tables/columns...)
• insert the data into the new database [Load]

Most ETLs also allow some kind of automatisation and reporting of the loads (bad/discarded rows...)

Oracle's ETL is called Oracle Warehouse Builder (OWB). It is included in the Database licence and you can download it from the Oracle website. As most Oracle products it is powerful but the learning curve is a bit steep.

You may want to look into the [ETL] section here in SO, among others:

• What ETL tool do you use?
• ETL tools… what do they do exactly? In laymans terms please.