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

From SQLZOO
Jump to: navigation, search
 
(One intermediate revision by one user not shown)
Line 6: Line 6:
 
We may want to find such long running processes and stop them. Some kind of administrative account is usually required.  
 
We may want to find such long running processes and stop them. Some kind of administrative account is usually required.  
 
</div>
 
</div>
<source lang=sql class='tidy'>
+
<source lang='sql' class='def'>SHOW PROCESSLIST;
 +
KILL 16318
 
</source>
 
</source>
<source lang=sql class='setup'></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
 
FORCE APPLICATION (294,306)</source>
 
FORCE APPLICATION (294,306)</source>
<source lang='sql' class='def e-ingres'></source>
 
<source lang='sql' class='def e-access'></source>
 
 
<source lang='sql' class='def e-postgres'>SELECT * FROM pg_stat_activity;
 
<source lang='sql' class='def e-postgres'>SELECT * FROM pg_stat_activity;
 
</source>
 
</source>
Line 42: Line 40:
 
<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>
 +
<source lang='sql' class='def e-ingres'></source>
 +
<source lang='sql' class='def e-access'></source>
  
 
<div class="ecomm e-db2" style="display: none">These commands may be executed by a system administrator using the CLP (command line processor - enter db2 at the prompt).</div>
 
<div class="ecomm e-db2" style="display: none">These commands may be executed by a system administrator using the CLP (command line processor - enter db2 at the prompt).</div>

Latest revision as of 10:16, 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.

SHOW PROCESSLIST;
KILL 16318
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