Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 17, 2021 - 11:06 am UTC

Last updated: November 19, 2021 - 1:31 am UTC

Version: 19c

Viewed 100+ times

You Asked

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
FROM settlement.t_calendar@dl_otcdb
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...

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

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database