RE: SQLcl no kill command?

From: Jeff Smith <jeff.d.smith_at_oracle.com>
Date: Fri, 22 Nov 2019 06:33:03 -0800 (PST)
Message-ID: <dc149524-c417-415b-b8e2-3fc13cd57108_at_default>

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> Sent: Friday, November 22, 2019 9:27 AM
To: Jacek Gębal <jgebal_at_gmail.com>
Cc: Mladen Gogala <gogala.mladen_at_gmail.com>; Kris Rice <kris.rice_at_oracle.com>; Jeff Smith <jeff.d.smith_at_oracle.com>; oracle-l-freelist <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 <HYPERLINK "mailto:jgebal_at_gmail.com"jgebal_at_gmail.com> wrote:

Would be a nice addition.

 

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

 

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

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
_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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 22 2019 - 15:33:03 CET