Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Refresh from Oracle


Refresh from Oracle

Author
Message
newbieuser
newbieuser
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 915
Hi friends,

We need to refresh the data from Oracle database to SQL Server 2005 every hour or so.. We have a linked server from SQL Server database to Oracle.. Can you please advice most efficient ways to do this?

Thanks much
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3625 Visits: 4639
newbieuser (8/6/2012)
We need to refresh the data from Oracle database to SQL Server 2005 every hour or so.. We have a linked server from SQL Server database to Oracle.. Can you please advice most efficient ways to do this?


How much data?
Are both servers on the same subnet?
What's the size of the pipe?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
emensj
emensj
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 295
Many times the most efficient way to do something is to avoid having to do it at all. In your case, trying to avoid dumping the entire table and loading it again.
Is it possible to only copy new records and/or delete old ones?
Can you use a view that points to the Oracle data?

What else can you tell us about what you are trying to do?
newbieuser
newbieuser
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 915
Thanks for your replies.. we need to migrate 3-4 tables in Oracle to MSSQL and refresh the data on an ongoing basis.
There are about 50000 records in each table and the script should be able to truncate the table first and then refresh the data from Oracle via the existing linked server..

We cannot use a view to point to the Oracle data as they are on different network and every time we query the view, it goes through the linked server and we are trying to avoid that.... Not sure if there is something like materialized views in MSSQl..

Please help..

Thanks a lot
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3625 Visits: 4639
newbieuser (8/21/2012)
Thanks for your replies.. we need to migrate 3-4 tables in Oracle to MSSQL and refresh the data on an ongoing basis.
There are about 50000 records in each table and the script should be able to truncate the table first and then refresh the data from Oracle via the existing linked server..

We cannot use a view to point to the Oracle data as they are on different network and every time we query the view, it goes through the linked server and we are trying to avoid that.... Not sure if there is something like materialized views in MSSQl..


For 50K rown in four to five tables I would probably consider doing it the easy way. Just write the T-SQL script on SQL Server side refreshing the tables through the linked server connection then schedule it as a job to automatically execute when appropriate.

As a side note, the closest thing to an Oracle Materialized View offered bh SQL Server is "Indexed View".

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search