Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to stop running an Oracle SQL command

Posted on 2006-11-16
3
1,701 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:amphastar
3 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17957637
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
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 125 total points
ID: 17958072
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
 

Author Comment

by:amphastar
ID: 17959578
Great. I killed the process with another connection to the database.

Thanks
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL - Query help 7 66
Alternative to GTT for a temp table for further reuse in Oracle 8 35
Oracle - Query link database loop 8 40
pivot rows to columns 1 34
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question