Take the 2-minute tour ×
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's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
What is to rollback, when the only statement was a complex select statement? –  bernd_k May 2 '11 at 14:49
    
Ah, that wasn't in the problem statement :-) I'll revise my answer, one moment... –  Gaius May 2 '11 at 16:37
    
I#M not inserting or updating anything in that schema. No use of global temporary tables. Why does a long running query create any redo/undo or what else? Are there hidden 'temporay tables' ? –  bernd_k May 3 '11 at 6:03
    
Oracle has to ensure that the version of the row that existed when your query started is kept for the duration of the query. This is called MVCC. Also are you using the TEMP tablespace at all for sorting? That will need to be cleaned up too. –  Gaius May 3 '11 at 7:52
    
There is a lot of analytical functions Rank() OVER (PARTITION –  bernd_k May 3 '11 at 7:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.