I just ran into this problem today. Everything was working fine with my linked server connection to the Oracle database (if you haven’t been following the saga.. read up on it) and then it just stopped working.
Anywho, I managed to get things working again by deleting the linked server and recreating it. This time though I made sure to the “Microsoft OLE DB Provider for Oracle” provider and made sure that “Allow In Process” was checked under the “Provider Options” from the advice I got here.
After doing that, everything is working once again.
I’m becoming a bit of an expert with linked servers to Oracle.