1 person found this helpful
It's up to the database to respond to the cancellation request and to actually stop the query/process.
I recently noticed that when I tried to stop/cancel a long-running query in SQL Developer, I see that it is still running on the server when I look in Oracle Enterprise Manager. Is this intentional?
Yes - when you give the server work to do it starts doing it. And unless/until it is finished (depending on the actual work requested) it will not try to communicate with the client until the work is done or there is an issue.
Of course while you were looking at EM you could also have just canceled that session. See the EM docs
The Hang Analysis page shows a visual map of the sessions that are either blocking or are blocked. A tree view of the sessions is displayed, and the problem session that is blocking other sessions is located at the root of the tree. Each session is shown color-coded, signifying how long the session has been blocked. Clicking on the session box brings up another page that shows session details. Using information from this page, you can cancel the problem session and return the system to its normal state.
Depending on what the 'query' is actually doing a simple 'KILL' on the session won' t necessarily stop it immediately either - the system may have to undo/rollback any DML changes that were being made.
Adding to what Jeff said, just focusing on queries, the odds of cancellation vary depending on your OS, JDBC driver version, whether it is Thin or OCI, etc. Here is an older discussion the covers lots of that territory: How to cancel long query?