Re: SQLcl no kill command?

From: Jack Applewhite <jack.applewhite_at_austinisd.org>
Date: Fri, 22 Nov 2019 15:37:00 +0000
Message-ID: <DM6PR19MB2603C4AB4405864E9F7C6775E6490_at_DM6PR19MB2603.namprd19.prod.outlook.com>

Even if Kill was a built-in, our Developers couldn't use it, 'cause we don't give 'em Alter System, which I assume would be required. Years ago, after too many kept bugging us to kill their runaway sessions, I put a System-owned KillMySession function in all the DBs. If the session you wanted to kill was the same OS User and same Oracle User, it would kill the other session, given its SID and Serial#. That satisfied 'em, greatly reduced demand on us, and prevented "innocent bystanders" from being whacked.

I was about to wish it was builtin to SQL Developer, which prompted me to go look at the 19.2 I'm using and, sure enough, in Tools / Monitor Sessions, when you right click on a session the "Kill Session" option is there. YAY! Glad I checked this thread out! SQLDev is just chock-full of goodies! Thanks, Jeff!
--

Jack C. Applewhite - Database Administrator Austin I.S.D. - MIS Department
512.414.9250 (wk)

I cannot help but notice that there is no problem between us that cannot be solved by your departure. -- Mark Twain

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of niall.litchfield_at_gmail.com <niall.litchfield_at_gmail.com> Sent: Friday, November 22, 2019 08:59
To: Jeff Smith <jeff.d.smith_at_oracle.com> Cc: Backseat DBA <backseatdba_at_gmail.com>; Jacek Gębal <jgebal_at_gmail.com>; Mladen Gogala <gogala.mladen_at_gmail.com>; Kris Rice <kris.rice_at_oracle.com>; oracle-l-freelist <oracle-l_at_freelists.org> Subject: Re: SQLcl no kill command?

<Friday snark>
Perhaps you could randomly select the instance, sid and serial# from those available and kill whatever session came up. The Russian Roulette function. Or you could at periodic intervals kill from 3-25 sessions. The mass shooting function. Or you could prevent all user sessions from doing anything useful for 3 years. The Brexit function. </snark>

I'm personally in favour of making this something that organisations have to build into their processes and procedures rather than being a built-in function of a tool that ends up on every developers desktop.

On Fri, Nov 22, 2019 at 2:34 PM Jeff Smith <jeff.d.smith_at_oracle.com<mailto:jeff.d.smith_at_oracle.com>> wrote:

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_at_gmail.com<mailto:backseatdba_at_gmail.com>> Sent: Friday, November 22, 2019 9:27 AM To: Jacek Gębal <jgebal_at_gmail.com<mailto:jgebal_at_gmail.com>> Cc: Mladen Gogala <gogala.mladen_at_gmail.com<mailto:gogala.mladen_at_gmail.com>>; Kris Rice <kris.rice_at_oracle.com<mailto:kris.rice_at_oracle.com>>; Jeff Smith <jeff.d.smith_at_oracle.com<mailto:jeff.d.smith_at_oracle.com>>; oracle-l-freelist <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: Re: SQLcl no kill command?

I second this!

On Fri, Nov 22, 2019 at 12:20 AM Jacek Gębal <jgebal_at_gmail.com<mailto:jgebal_at_gmail.com>> wrote:

Would be a nice addition.

On Fri, 22 Nov 2019, 02:21 Mladen Gogala, <gogala.mladen_at_gmail.com<mailto:gogala.mladen_at_gmail.com>> 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 <jeff.d.smith_at_oracle.com><mailto:jeff.d.smith_at_oracle.com> wrote:



I don’t think we ever shipped that with the product, but Kris built it via a js script custom command

http://krisrice.io/2016-06-22-kill-db-sessions-easy-way-with-sqlcl/<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=>

Jeff

From: Mladen Gogala <gogala.mladen_at_gmail.com><mailto:gogala.mladen_at_gmail.com> Sent: Thursday, November 21, 2019 3:57 PM To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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:

/
_at_
_at__at_
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:

http://krisrice.io/2016-06-22-kill-db-sessions-easy-way-with-sqlcl/<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=>

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
_at_/tmp/murder
exit;

And I was already looking forward to the easy way. Also, "_at_" doesn't work unless I set SQLPATH.

--

Mladen Gogala

Database Consultant

Tel: (347) 321-1217

--

Niall Litchfield
Oracle DBA
http://www.orawin.info
Confidentiality Notice: This email message, including all attachments, is for the sole use of the intended recipient(s) and may contain confidential student and/or employee information. Unauthorized use of disclosure is prohibited under the federal Family Educational Rights & Privacy Act (20 U.S.C. §1232g, 34 CFR Part 99, 19 TAC 247.2, Gov’t Code 552.023, Educ. Code 21.355, 29 CFR 1630.14(b)(c)). If you are not the intended recipient, you may not use, disclose, copy or disseminate this information. Please call the sender immediately or reply by email and destroy all copies of the original message, including attachments.
--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 22 2019 - 16:37:00 CET