Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
When it stops the execution of a long running script. The script terminating needs even longer then the time the script was running. I.e. I have to kill Toad.
2nd EDIT:
Just found that this is a feature of Toad and not of Oracle
In SQL developer, you can stop execution of long running queries and similar to SQL Server Management Studio you get can modify your query immediately.
Trace your session. I expect you will find Toad is waiting for Oracle to roll back your transaction - which Oracle will continue to do in the background even if Toad is killed.
Under the covers, when you abort a query, an Oracle client application sends OCIBreak(). However in order to actually process this, the shadow process on the Oracle side has to come back and read that message - it is a request only, there is no way to send a true interrupt (i.e. a signal in Unix terms) across the network. If the shadow process is in a state where it is itself waiting for an operation to complete (such as a big I/O request to the OS) then it is not until that completes that it can advance to the next "tick" and find the break command waiting for it.
If you are really impatient, you could get the same result by finding the shadow process (v$process.spid where addr = v$session.paddr and sid=<your sid>) on the server and just issuing a kill command at the shell.
Incidentally there is some clean-up to do when a long-running SELECT is aborted: the previous versions of the rows are no longer needed.
