We’ll of course need a
Are you sure?
Are you really, really sure?
And then someone will still complain they murdered the wrong person.
Joking about the confirmation prompts…mostly.
From: Jeff Chirco <backseatdba@xxxxxxxxx>
Sent: Friday, November 22, 2019 9:27 AM
To: Jacek Gębal <jgebal@xxxxxxxxx>
Cc: Mladen Gogala <gogala.mladen@xxxxxxxxx>; Kris Rice <kris.rice@xxxxxxxxxx>;
Jeff Smith <jeff.d.smith@xxxxxxxxxx>; oracle-l-freelist <oracle-l@xxxxxxxxxxxxx>
Subject: Re: SQLcl no kill command?
I second this!
On Fri, Nov 22, 2019 at 12:20 AM Jacek Gębal <HYPERLINK
"mailto:jgebal@xxxxxxxxx"jgebal@xxxxxxxxx> wrote:
Would be a nice addition.
On Fri, 22 Nov 2019, 02:21 Mladen Gogala, <HYPERLINK
"mailto:gogala.mladen@xxxxxxxxx"gogala.mladen@xxxxxxxxx> wrote:
Maybe it could be added in version 20.1?
On 11/21/19 8:42 PM, Kris Rice wrote:
Correct there was never a kill but that blogpost shows how to add a kill
command.
-k
On Nov 21, 2019, at 16:22, Jeff Smith HYPERLINK
"mailto:jeff.d.smith@xxxxxxxxxx";<jeff.d.smith@xxxxxxxxxx> wrote:
I don’t think we ever shipped that with the product, but Kris built it via a js
script custom command
HYPERLINK
"https://urldefense.proofpoint.com/v2/url?u=http-3A__krisrice.io_2016-2D06-2D22-2Dkill-2Ddb-2Dsessions-2Deasy-2Dway-2Dwith-2Dsqlcl_&d=DwMFaQ&c=RoP1YumCXCgaWHvlZYR8PZh8Bv7qIrMUB65eapI_JnE&r=N2hWu5HFsaIjmMkjQbnlokJ7uinNZMgPVk8rqPT9esM&m=rzLrgzSLaQI-x1RRP_Qpgw9uYXZpg2ECP2AVlp2SN5I&s=ewm8dgj1VOtvCbk74oKgHRVjrIqoPDhmvNgS-1SL0Zw&e="http://krisrice.io/2016-06-22-kill-db-sessions-easy-way-with-sqlcl/
Jeff
From: Mladen Gogala HYPERLINK
"mailto:gogala.mladen@xxxxxxxxx";<gogala.mladen@xxxxxxxxx>
Sent: Thursday, November 21, 2019 3:57 PM
To: HYPERLINK "mailto:oracle-l@xxxxxxxxxxxxx"oracle-l@xxxxxxxxxxxxx
Subject: SQLcl no kill command?
One of the nice features of SQLcl 17 was the existence of the kill command
which allowed me to murder all sessions belonging to the particular user. SQLcl
19.2 can no longer do that:
SQL> help
For help on a topic type help <topic>
List of Help topics available:
/
@
@@
ACCEPT
ALIAS*
APEX*
APPEND
ARCHIVE_LOG
BREAK
BRIDGE*
BTITLE
CD*
CHANGE
CLEAR
CODESCAN*
COLUMN
COMPUTE
CONNECT
COPY
CTAS*
DDL*
DEFINE
DEL
DESCRIBE
DISCONNECT
EDIT
EXECUTE
EXIT
FIND*
FORMAT*
GET
HISTORY*
HOST
INFORMATION*
INPUT
LB*
LIQUIBASE*
LIST
LOAD*
NET*
OERR*
PASSWORD
PAUSE
PRINT
PROMPT
QUIT
REMARK
REPEAT*
RESERVED_WORDS
REST*
RUN
SAVE
SCRIPT*
SET
SETERRORL
SHOW
SHUTDOWN
SODA*
SPOOL
SSHTUNNEL*
START
STARTUP
STORE
TIMING
TNSPING*
TTITLE
UNDEFINE
VARIABLE
VAULT*
WHENEVER
WHICH*
XQUERY
SQL>
Kill is no longer listed among the available commands. The command was
documented on Kris Rice's blog:
HYPERLINK
"https://urldefense.proofpoint.com/v2/url?u=http-3A__krisrice.io_2016-2D06-2D22-2Dkill-2Ddb-2Dsessions-2Deasy-2Dway-2Dwith-2Dsqlcl_&d=DwMDaQ&c=RoP1YumCXCgaWHvlZYR8PZh8Bv7qIrMUB65eapI_JnE&r=N2hWu5HFsaIjmMkjQbnlokJ7uinNZMgPVk8rqPT9esM&m=Bdfgt5NDwZ4IeECHTDvSr9icrVj7qO0PQxJfGaUl_4E&s=STA8PbIbB0XYkToOSp4-7gN4RP8dI2Ynjz2fhmDKBWw&e="http://krisrice.io/2016-06-22-kill-db-sessions-easy-way-with-sqlcl/
Now, I have to go at it again:
define username=lower('&user');
set trimout on
set trimspool on
set pagesize 0
set echo off
set termout off
spool /tmp/murder.sql
select 'ALTER SYSTEM KILL SESSION '''||sid||','||SERIAL#||''';'
from v$session where lower(username)=&username;
spool off
@/tmp/murder
exit;
And I was already looking forward to the easy way. Also, "@" doesn't work
unless I set SQLPATH.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
