Connor at 13:34, 17 July 2012

2012-07-17T13:34:09Z

Connor: Created page with "Change the default schema/database.

schema:gisq

Sometimes users set off queries that may take a very long time to complete. We m..."

2012-07-17T13:26:15Z

Created page with "Change the default schema/database. <div class='ht'> <div class=params>schema:gisq</div> <div> Sometimes users set off queries that may take a very long time to complete. We m..."

New page

Change the default schema/database.
<div class='ht'>
<div class=params>schema:gisq</div>
<div>
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.
</div>
<source lang=sql class='tidy'>
</source>
<source lang=sql class='setup'></source>
<source lang='sql' class='def e-sqlite'></source>
<source lang='sql' class='def e-db2'>LIST APPLICATION;
-- 294 and 306 are "Appl Handle" values
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>
<source lang='sql' class='def e-oracle'>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#
<source lang='sql' class='def e-sqlserver'>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;
</source>
<source lang='sql' class='def e-mysql'>SHOW PROCESSLIST;
KILL 16318
</source>
<source lang='sql' class='def e-mimer'></source>
<source lang='sql' class='def e-sybase'></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-mysql" style="display: none">Use the KILL command. Does what it says.</div>
<div class="ecomm e-mimer" style="display: none">We can list the processes with the operating system command
miminfo -u
We can kill process number 123 in database GISQ with the command
mimcontrol -l 123 GISQ</div>
<div class="ecomm e-sqlserver" style="display: none">sysprocesses in the master database includes all the SQL statements that are currently running. @@spid is your process. @var1 is a local variable.</div>
</div>
{{Users ref}}

@@ Line 22: / Line 22: @@
 ALTER SYSTEM KILL SESSION '12,33'
 -- 12,33 is the sid and serial#
 <source lang='sql' class='def e-sqlserver'>USE master;
 SELECT spid , nt_username,

Find another process and kill it. - Revision history

Connor at 13:34, 17 July 2012

Connor: Created page with "Change the default schema/database. schema:gisq Sometimes users set off queries that may take a very long time to complete. We m..."

Connor: Created page with "Change the default schema/database.

schema:gisq

Sometimes users set off queries that may take a very long time to complete. We m..."