Difference between revisions of "Find another process and kill it."

From SQLZOO
Jump to: navigation, search
Line 9: Line 9:
 
</source>
 
</source>
 
<source lang=sql class='setup'></source>
 
<source lang=sql class='setup'></source>
<source lang='sql' class='def e-sqlite'></source>
 
 
<source lang='sql' class='def e-db2'>LIST APPLICATION;
 
<source lang='sql' class='def e-db2'>LIST APPLICATION;
 
-- 294 and 306 are "Appl Handle" values
 
-- 294 and 306 are "Appl Handle" values
Line 40: Line 39:
 
KILL 16318
 
KILL 16318
 
</source>
 
</source>
 +
<source lang='sql' class='def e-sqlite'></source>
 
<source lang='sql' class='def e-mimer'></source>
 
<source lang='sql' class='def e-mimer'></source>
 
<source lang='sql' class='def e-sybase'></source>
 
<source lang='sql' class='def e-sybase'></source>

Revision as of 09:14, 7 March 2014

Change the default schema/database.

schema:gisq

Sometimes users set off queries that may take a very long time to complete. We may want to find such long running processes and stop them. Some kind of administrative account is usually required.

 
 
LIST APPLICATION;
-- 294 and 306 are "Appl Handle" values
FORCE APPLICATION (294,306)
 
 
SELECT * FROM pg_stat_activity;
SELECT sid, serial#, username,
       TO_CHAR(logon_time,'Month dd hh24:mi:ss')
  FROM sys.v_$session;
ALTER SYSTEM KILL SESSION '12,33'
-- 12,33 is the sid and serial#
USE master;
SELECT spid , nt_username,
       datediff(s,login_time,GETDATE())
   FROM sysprocesses;
DECLARE @var1 INT
SELECT @var1 = spid 
  FROM sysprocesses
 WHERE nt_username='andrew'
   AND spid<>@@spid
EXEC ('kill '+@var1);
SELECT spid , nt_username,
       datediff(s,login_time,GETDATE())
   FROM sysprocesses;
SHOW PROCESSLIST;
KILL 16318
 
 
 
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense