How to stop running an Oracle SQL command

I am running a Oracle 8i on a windows 2000 environment.
I tried to run a SQL command in the SQL*plus worksheet. When I try to display the contents in a table using "select", I forgot to put in the "where" clause which resulted in the command running for a long long time because it is a huge table. Is there any way I can stop the command before it finishes?

Thanks
amphastarAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
I haven't used SQL*Plus Worksheet either.  One option that works for any Oracle program is to start a new SQL*Plus (or TOAD, or SQL*PLUS Worksheet, or any other utility that connects to Oracle and allows you to execute SQL statements) then do a "kill session..." statement for the session you want to kill.  I use this script in SQL*Plus for that:

(Note, you do need to be a DBA or have the "alter system" privilege to do this.)

column sid format 9999;
column serial# format 9999999;
column "Logon" format a13;
set verify off;
select s.sid, s.serial#, nvl((select p.spid from v$process p where p.addr = s.paddr),'  ?') "Spid",
substr(s.terminal,9,5) "Type",substr(s.machine,instr(s.machine,'\') +1,8) "Machine", s.status, s.server,
rpad(substr(decode(s.program,'OraPgm','Windows95 unknown',s.program),greatest(
 (length(decode(s.program,'OraPgm','Windows unknown',s.program)) - 23),1),24),24,' ') "Program",
substr(s.osuser,instr(s.osuser,'\') +1,14) "OsUser", to_char(s.logon_time,'YYMMDD HH24MISS') "Logon"
from v$session s where s.username = upper('&username');
--
Prompt Enter the "SID" and "SERIAL#" values to cancel a session...
alter system kill session '&SID,&SERIAL';
set verify on;
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i have not worked on sql*plus worksheet, but in sql*plus we can try ctrl + c or esc key something like that.

I think some expert on sql*plus worksheet has to hit this one.

Thanks
0
 
amphastarAuthor Commented:
Great. I killed the process with another connection to the database.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.