Oracle Linked Server Hangs

[kkbradsh]

I have a stored procedure that occasionally hangs with a wait type of OLEDB. I can try to kill the process, but it remains for days with status of KILLED/ROLLBACK. The only resolution I've found so far is to reboot the server, which is not a solution for a production server.

The stored procedure uses OPNEQUERY to load an SQL Server table from an Oracle database. Even after the stored procedure hangs, I can continue to use OPENQUERY to access the Oracle database; but, I can't access the SQL Server table which is locked by the KILLED/ROLLBACK process.

I also have DTS packages that use Microsoft OLEDB driver for Oracle to pull data from same Oracle database. The DTS jobs will occasionally hang as well. Once one job hangs, all DTS jobs that use the Oracle driver also hang. But, once I kill the jobs, I am able to rerun the jobs without further issues and the processes clear from sysprocesses.

Any ideas? I suspect that the DTS is running out-of-process while the OPENQUERY stored procedure is running in-process. I also suspect that the connectivity to the Oracle server is lost which causes the hang in the first place.

Thanks in advance.

Kim