Staging table between 9i & 10gR2 [message #316828] |
Mon, 28 April 2008 08:44  |
ranj_chn
Messages: 3 Registered: April 2008
|
Junior Member |
|
|
We are having a situation where we need to get data from a 10gR2 Db and load it into a 9i DB. We should be using staging tables inbetween for the transfer of data.
The steps are like this -
1) A trigger fires on the 10gR2 DB table based on insert/update happening and inserts into a staging table.
2) The data from the staging table is processed and loaded into the 9i tables.
I was wondering where should I create these staging tables? Should it be on the 9i side so that only the insert that happens from triggers on 10g DB tables do it via the network. Or should I have the staging tables on the 10g side and create DB links for subsequent processing? Or it doesnt make any difference to create a staging table on any of the DB's.
Please guide me. I didnt know where to exactly post this and so I have posted here. Moderators, please correct me if its incorrect.
|
|
|
|
|
Re: Staging table between 9i & 10gR2 [message #316834 is a reply to message #316832] |
Mon, 28 April 2008 09:00   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Also, think about uptime issues.
When the databases are on different machines and the 10g is a production database and the 9g is a Data Warehouse, then a crashed Data Warehouse DB and/or network connection might bring your production Application down if the staging tables are on 9g and inserted over an dblink and the trigger can't execute anymore.
So I generally prefer the staging tables to be on the source database.
|
|
|
|
Re: Staging table between 9i & 10gR2 [message #316838 is a reply to message #316836] |
Mon, 28 April 2008 09:12  |
ranj_chn
Messages: 3 Registered: April 2008
|
Junior Member |
|
|
The application that uses 10gR2 DB has a nightly batch that triggers these changes. The 10gR2 table holds around 1 million records and less than 10% of these records would undergo changes evey night. Thats the basic minimum information I have right now.
|
|
|