We have an Oracle 19c database where a complex query runs fine for all users except one. When this specific user executes the query, it freezes indefinitely with no errors, no active locks (v$locked_object shows nothing), and no significant wait events in v$session.
Troubleshooting so far:
- The query plan remains the same for all users.
- Running the same query from another user with identical privileges works instantly.
- No resource limits (
profile) are restricting the user.
- Even after recreating the user and reassigning privileges, the issue persists.
v$session_wait only shows “SQL*Net message from client,” indicating no obvious internal waits.
What could be causing this issue? Could it be related to session-level settings, optimizer hints, or an obscure resource contention problem? Any insightful response would be appreciated!