#!/bin/sh
# Copyright (c) 2023 Guillaume Outters
# 
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
# 
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
# 
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.  IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.

# Bute un processus Oracle après sélection.
# N.B.: si le processus père apparaît en 1234 c'est qu'il est déjà en cours de piqûre, en attente de libération des ressources par ex. autres instances participant à un parallel().

sqlo() { … }
ssho() { … }

sqlo "
	select
		'[32m'||to_char(a.sql_exec_start, 'YYYY-MM-DD HH24:MI:SS')||'[0m' debut, a.process pid, '[31m'||spid||'[0m' spid, a.sid, a.serial#, a.username,b.sql_id,
		wait_class, wait_time_micro, blocking_session_status, '[33m'||event||'[0m' event,
		replace(chr(10)||substr(b.sql_fulltext, 1, 99999), chr(10), chr(10)||chr(9))||chr(10)
		||(select coalesce('[ '||listagg(p.name||' = '||p.value_string, ', ') within group (order by position, dup_position)||' ]'||chr(10), '') from v\$sql_bind_capture p where p.sql_id = b.sql_id) req,
	    a.*
	from v\$session a, v\$sql b, v\$process p
	where a.sql_id = b.sql_id and a.status = 'ACTIVE' and a.username != 'SYS'
	and p.addr = a.paddr
	--and substr(b.sql_fulltext, 1, 300) not like '%fulltext%9999%' -- Ça te me tue les perfs, même avec le substr.
	and a.audsid <> userenv('sessionid')
	order by sql_exec_start, a.process
" | tee /tmp/reqlongues.sql | less
printf "Lequel buter? " ; read cible
ssho "kill $cible ; sleep 5 ; kill -9 $cible"
