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.