Session sometimes hangs when doing select in stored procedure

A stored package procedure executes a SELECT, a join of a local table and a local view. The local view is based on tables in a remote database through a db-link. The "remote" database is running on the same physical server as the "local" database. Several other packages and queries are executing successfully all the time against the remote database.

The stored procedure seems to work around 75% of the time. The rest of the time, however, the session just hangs indefinitely. It also locks the package that was running. The session cannot be killed with ALTER SYSTEM KILL SESSION, and the database must be restarted to remove it and release the lock on the package.

Has anyone experienced a similar situation? This is very frustrating since the session hangs with no error message or indication of what has gone wrong. I can even run the stored procedure or query on its own (outside of the package) without the session locking up.

I'm using Oracle 7.3.4 Received on Mon Sep 06 1999 - 10:31:46 CDT