How to kill a running SELECT statement

Question

How can I stop a running SELECT statement by killing the session   The command is continuously giving me output based on the SELECT statement  I want to stop it in between

User · Answer

Oh  just read comments in question  dear I missed it  but just letting the answer be here in case it can be useful to some other person  I tried  Ctrl C  and  Ctrl  Break  none worked  I was using SQL Plus that came with Oracle Client 10 2 0 1 0  SQL Plus is used by most as client for connecting with Oracle DB  I used the Cancel  option under File menu and it stopped the execution     Once you click File wait for few mins then the select command halts and menu appears click on Cancel

User · Answer

As you keep getting pages of results I m assuming you started the session in SQL Plus  If so  the easy thing to do is to bash ctrl   break many  many times until it stops   The more complicated and the more generic way s  I detail below in order of increasing ferocity   evil  The first one will probably work for you but if it doesn t you can keep moving down the list    Most of these are not recommended and can have unintended consequences     1  Oracle level - Kill the process in the database  As per ObiWanKenobi s answer and the ALTER SESSION documentation  alter system kill session  sid serial      To find the sid  session id  and the serial   serial number  run the following query - summarised from OracleBase - and find your session   select s sid  s serial   p spid  s username  s schemaname        s program  s terminal  s osuser   from v session s   join v process p     on s paddr   p addr  where s type     BACKGROUND    If you re running a RAC then you need to change this slightly to take into account the multiple instances  inst id is what identifies them   select s inst id  s sid  s serial   p spid  s username        s schemaname  s program  s terminal  s osuser   from Gv session s   join Gv process p     on s paddr   p addr    and s inst id   p inst id  where s type     BACKGROUND    This query would also work if you re not running a RAC   If you re using a tool like PL SQL Developer then the sessions window will also help you find it   For a slightly stronger  kill  you can specify the IMMEDIATE keyword  which instructs the database to not wait for the transaction to complete   alter system kill session  sid serial   immediate    2  OS level - Issue a SIGTERM  kill pid   This assumes you re using Linux or another  nix variant  A SIGTERM is a terminate signal from the operating system to the specific process asking it to stop running  It tries to let the process terminate gracefully   Getting this wrong could result in you terminating essential OS processes so be careful when typing   You can find the pid  process id  by running the following query  which ll also tell you useful information like the terminal the process is running from and the username that s running it so you can ensure you pick the correct one   select p     from v process p   left outer join v session s     on p addr   s paddr  where s sid        and s serial        Once again  if you re running a RAC you need to change this slightly to   select p     from Gv process p   left outer join Gv session s     on p addr   s paddr  where s sid        and s serial        Changing the where clause to where s status    KILLED  will help you find already killed process that are still  running    3  OS - Issue a SIGKILL  kill -9 pid   Using the same pid you picked up in 2  a SIGKILL is a signal from the operating system to a specific process that causes the process to terminate immediately  Once again be careful when typing    This should rarely be necessary  If you were doing DML or DDL it will stop any rollback being processed and may make it difficult to recover the database to a consistent state in the event of failure      All the remaining options will kill all sessions and result in your database - and in the case of 6 and 7 server as well - becoming unavailable  They should only be used if absolutely necessary      4  Oracle - Shutdown the database  shutdown immediate   This is actually politer than a SIGKILL  though obviously it acts on all processes in the database rather than your specific process  It s always good to be polite to your database    Shutting down the database should only be done with the consent of your DBA  if you have one  It s nice to tell the people who use the database as well   It closes the database  terminating all sessions and does a rollback on all uncommitted transactions  It can take a while if you have large uncommitted transactions that need to be rolled back    5  Oracle - Shutdown the database   the less nice way    shutdown abort   This is approximately the same as a SIGKILL  though once again on all processes in the database  It s a signal to the database to stop everything immediately and die - a hard crash   It terminates all sessions and does no rollback  because of this it can mean that the database takes longer to startup again  Despite the incendiary language a shutdown abort isn t pure evil and can normally be used safely   As before inform people the relevant people first   6  OS - Reboot the server  reboot   Obviously  this not only stops the database but the server as well so use with caution and with the consent of your sysadmins in addition to the DBAs  developers  clients and users   7  OS - The last stage  I ve had reboot not work    Once you ve reached this stage you better hope you re using a VM  We ended up deleting it

User · Answer

If you want to stop process you can kill it manually from task manager onother side if you want to stop running query in DBMS you can stop as given here for ms sqlserver T-SQL STOP or ABORT command in SQL Server Hope it helps you

User · Answer

This is what I use   I do this first query to find the sessions and the users   select s sid  s serial   p spid  s username  s schemaname        s program  s terminal  s osuser   from v session s   join v process p     on s paddr   p addr  where s type     BACKGROUND     This will let me know if there are multiple sessions for the same user   Then I usually check to verify if a session is blocking the database   SELECT SID  SQL ID  USERNAME  BLOCKING SESSION  COMMAND  MODULE  STATUS FROM v session WHERE BLOCKING SESSION IS NOT NULL      Then I run an ALTER statement to kill a specific session in this format   ALTER SYSTEM KILL SESSION  sid serial       For example   ALTER SYSTEM KILL SESSION  314  2643

User · Answer

There is no need to kill entire session  In Oracle 18c you could use ALTER SYSTEM CANCEL      Cancelling a SQL Statement in a Session      You can cancel a SQL statement in a session using the ALTER SYSTEM CANCEL SQL statement       Instead of terminating a session  you can cancel a high-load SQL statement in a session  When you cancel a DML statement  the statement is rolled back   ALTER SYSTEM CANCEL SQL  SID  SERIAL    INST ID    SQL ID          If  INST ID is not specified  the instance ID of the current session is used       If SQL ID is not specified  the currently running SQL statement in the specified session is terminated

User · Answer

To kill a session in Oracle  you use the ALTER SYSTEM KILL SESSION command   Details here  http   www oracle-base com articles misc KillingOracleSessions php

[sql] How to kill a running SELECT statement

Examples related to sql

Examples related to oracle

Examples related to select

Examples related to oracle10g

Examples related to sqlplus