How can I cancel a query? 1

[Welshbird]

Is there an easy way to cancel a query once it is running in SQL*Plus?



Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[oradba101]

Hi, Willif

I believe "Ctrl-C" should do it (from Windows anyways).

Regards,


William Chadbourne
Oracle DBA

 

[SantaMufasa]

Willi,

Unfortunately, Oracle did not design a good interrupt scheme for SQL*Plus. Although a <ctrl-C> can eventually cause an interrupt, it usually is too late to do any good since SQL*Plus does not detect the <ctrl-C> until it has finished outputting its current result-set package from the server.

When I wish to interrupt a long-running query (and if the <ctrl-C> does not respond before I have lost patience), I just kill the client SQL*Plus window that is running the query. The Oracle PMON (Process MONitor) process cleans up your interrupted server process.

Sorry that I don't have better news for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Welshbird]

Thanks guys - that was what I thought (or at least what I was doing!) but I wasn;t sure if there was a better options.

Best be checking my query before it runs then!!!



Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[SantaMufasa]

Willi,

If I have an untested query that could run indefinitely, I typically test the query initially with this condition temporarily as part of the WHERE clause:

WHERE ROWNUM <= 10

This allows only the first ten rows that Oracle encounters in the FROM clause to be part of the result set. This prevents a query from becoming a "run-away" query.

Let us know if this is useful for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Welshbird]

Thanks for this. You are always SO helpful!



Fi.

"The question should be, is it worth trying to do, not can it be done"