Post reply

Refresh from Oracle

  • August 6, 2012 at 7:51 am

    #392899

    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

  • August 8, 2012 at 11:05 am

    #1523264

    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.

  • August 9, 2012 at 12:17 pm

    #1524024

    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?

  • August 21, 2012 at 5:53 am

    #1528067

    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

  • August 22, 2012 at 7:30 am

    #1528627

    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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply