Home » SQL & PL/SQL » SQL & PL/SQL » need to export 2 million records
need to export 2 million records [message #211272] Wed, 27 December 2006 13:43 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
ok, guys, need some advise Smile I have 2 databases whereby I have 20 identical tables in both. The idea is that if one db is down, our app will still have data available to it via 2 nd database. Ok, so I need to run a script that would populate these 20 tables with data from the original database. Say I will be running this script every 2 hours or on demand (or we havent decided that yet Smile ). Anyway, one of the tables has > 2 million rows. What would be the easiest and most efficient way to populate this table without hurting the performance? I am actually thinking about creating materialized view and joining this 2 mil record table with another table to have a view of data that I need. However, I ran in a problem in the past whereby the view is not available while its being refreshed. So, is there any other way (aside from populating this big table directly)?

any ideas would be greatly appreciated. thanx!
Re: need to export 2 million records [message #211275 is a reply to message #211272] Wed, 27 December 2006 13:45 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You might want to consider using "Change Data Capture".
Re: need to export 2 million records [message #211278 is a reply to message #211275] Wed, 27 December 2006 14:53 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
This looks like a great idea! I am going to read up on it. Thanx!
Re: need to export 2 million records [message #211283 is a reply to message #211278] Wed, 27 December 2006 15:41 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Also read up on "dataguard" for 8i/9i/10g

http://www.oracle.com/technology/deploy/availability/htdocs/DataGuardOverview.html

Re: need to export 2 million records [message #211285 is a reply to message #211283] Wed, 27 December 2006 16:04 Go to previous message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
thanx for the info! my only problem is that the users would like to refresh the data on demand - i.e. push the button, data gets refreshed via db 1. If db1 is down, then refresh it via db 2, which is updated with new data from db 1, say every hour or so...
Previous Topic: Problem with SQL to join Requisitions to Purchase Orders
Next Topic: difference between functions and stored procedures
Goto Forum:
  


Current Time: Sun Dec 04 16:44:55 CST 2016

Total time taken to generate the page: 0.12095 seconds