Hi, I have a simple sql like "SELECT max(case when day=:1 then tra else 0 end) tra,
max(case when tra=1 and day<:2 then day else '0' end) lasttradingday
WHERE day <= :3 ". It query blocked offen. I cannot find any blocking session in v$session. After killed this session, it works after query again. How could it be blocked? it query at 19c, and remote database is 11g.
and Connor said...
Is this answer out of date? If it is, please let us know via a Comment
The most common issue for the cross-db query to be "blocked" is that there has been a network interruption of similar, and the database has "lost contact" with its partner.
You can tell by connecting to the remote database and looking for active sessions where the client is your source database (TERMINAL/MACHINE/PROGRAM in v$session).
If that session on the remote is idle, it means we're no longer getting data from it. Similarly, on your local session, look at the current event it is waiting on v$session. If it is "SQL*Net" related, its likely related to either network timeout or network slowdown.
You could explore using Resource Manager to kill this query after a timeout https://asktom.oracle.com/pls/apex/asktom.search?tag=cancelling-long-running-queries