Microsoft SQL linked server to Oracle data not updating

Question

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.

• Aaron Bertrand: Do you have some kind of caching layer implemented on the Oracle side?
• BenVlodgi: @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.

Answer 1

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.

• Aaron Bertrand: 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.
• Spörri: @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.