8

Is there any way to stop or terminate long running Oracle query in JDBC ? It often end up with restart application server to get jdbc to disconnect from Oracle DB.

Looking for functionality similar to SQL Plus - Kill session in Java or JDBC

in SQL Plus

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

any clue to perform in java ?

AzizSM
  • 6,199
  • 4
  • 42
  • 53
  • 1
    [This solution](http://stackoverflow.com/questions/1526211/how-to-cancel-a-postgres-query-in-java-jdbc) is maybe implemented by the Oracle driver. – assylias Jun 08 '12 at 10:43
  • 3
    Yes, it should be. See [this question](http://stackoverflow.com/questions/656162/when-i-call-preparedstatement-cancel-in-a-jdbc-application-does-it-actually-k). – eis Jun 08 '12 at 10:45

3 Answers3

8

short of the jdbc timeout, if you run your query in another thread, you can use Statement.cancel() to kill it.

Matt
  • 11,523
  • 2
  • 23
  • 33
6

Possibly bit off, but I would propose using JDBC Timeout instead. It has the added benefit of actually terminating gracefully instead of by force.

With modern app servers you can usually define JDBC timeout from datasource configuration.

eis
  • 51,991
  • 13
  • 150
  • 199
  • 2
    I'd like to let the user to decide when to terminate the query. Can that be changed on runtime and implement something like sliding timeout? – supertonsky Jun 06 '17 at 09:09
  • @supertonsky I'm not aware of such functionality. – eis Jun 06 '17 at 09:57
  • does it actually stops the query running on db or just stop waiting for the output and throws exception ? – JustTry Jul 27 '22 at 21:30
  • @JustTry it will send a request to the database to abort the query/connection and then close it. There are multiple different types of JDBC timeouts which act in bit different ways (query timeout, connect timeout, socket timeout, network timeout...), but some more details about what happens is available for example [here](https://www.cubrid.org/blog/3826470) – eis Aug 01 '22 at 10:06
2

You can try Statement.setQueryTimeout

Yongxin Zhao
  • 124
  • 1