4

I need to pull data from an Oracle database running on a remote machine and use it on my sql server instance. I need to check these tables periodically for updated or new records.

I have created a linked server connection to the oracle db. I am able to select data from tables using the following syntax.

SELECT * FROM OPENQUERY(CUSTOMER_HOST, 'SELECT * FROM dc_15.MISSIONPLAN')

This works, I get the data I expect. However I noticed when the data on the remote server changes (adding, removing or modifying records) the changes aren't selected. The same data is pulled as before. If I alter the table on the oracle db, and run the select again, I get the most recent data. However now the data seems to be frozen again in this state, unless I alter the table again.

Is there some sort of refresh I'm supposed to invoke on the sql server side? Or is there something I need to have triggered on the oracle db to say the data has been updated?

To interface with the DBs I'm using Miscrosoft SQL Server Management Studio, and Oracle SQL Developer.

Improve this question
2
  • 1
    Do you have some kind of caching layer implemented on the Oracle side? Commented May 26, 2015 at 16:21
  • @AaronBertrand Not as far as I know, however your comment made me think more about the differences between oracle and microsoft's tools, and the transactions. Which then pointed me to the stupidly obvious answer. Commented May 26, 2015 at 16:34

1 Answer 1

Reset to default
2

I feel really stupid for not thinking about this earlier. I'm so used to SQL Management Studio not requiring changes to be committed. I also thought that the changes were being committed on oracle, because my select statements reflected the changes made.

I found the solution to my problem was upon changing data in Oracle SQL Developer I needed to explicitly 

commit;

Now the updated data is available through my linked server.

Improve this answer
2
  • 1
    And altering the table in Oracle forced a commit? That seems like strange behavior - I would expect altering the table to be blocked while there is an open transaction against the table. Commented May 26, 2015 at 16:45
  • 1
    @AaronBertrand This is Oracle, the open transaction is in the redo log buffer until committed. an ALTER statement will automatically commit (docs.oracle.com/cd/E17952_01/refman-5.1-en/implicit-commit.html) and in doing so will commit all statements from the same session. Commented May 27, 2015 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.