Home » SQL & PL/SQL » SQL & PL/SQL » Staging table between 9i & 10gR2 (Oracle 9i & 10gR2)
Staging table between 9i & 10gR2 [message #316828] Mon, 28 April 2008 08:44 Go to next message
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 #316832 is a reply to message #316828] Mon, 28 April 2008 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It depends on your processing and environment.
Is it CPU crunching?
Is your source CPU overloaded?
Does your processing reduce the data?
...

Regards
Michel
Re: Staging table between 9i & 10gR2 [message #316833 is a reply to message #316828] Mon, 28 April 2008 08:59 Go to previous messageGo to next message
ranj_chn
Messages: 3
Registered: April 2008
Junior Member
Actually I am thinking of an approach here. I donot have access to the environment yet. I am thinking what all should I keep in mind before taking a decision and is there any best practice in that?
Re: Staging table between 9i & 10gR2 [message #316834 is a reply to message #316832] Mon, 28 April 2008 09:00 Go to previous messageGo to next message
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 #316836 is a reply to message #316828] Mon, 28 April 2008 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Also what must be the refresh frequency in the target database?

Regards
Michel
Re: Staging table between 9i & 10gR2 [message #316838 is a reply to message #316836] Mon, 28 April 2008 09:12 Go to previous message
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.
Previous Topic: problem procedure
Next Topic: Select job, empno from scott.emp but distinct job ?
Goto Forum:
  


Current Time: Sun Feb 09 21:17:07 CST 2025