Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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 1000+ 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


We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

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