corrigindo:
select '!kill -9 '|| p.spid FROM gv$session s, gv$process p WHERE s.paddr =
p.addr AND s.username IS NOT NULL AND s.status like '%KILLED%';
como faco pra dentro do shell, ele executar a saida do sql dinamico?
Em Terça-feira, 12 de Dezembro de 2017 12:21, "Rafael Mendonca
raffaell.t...@yahoo.com [oracle_br]" <oracle_br@yahoogrupos.com.br> escreveu:
Sergio, obrigado pela resposta, mas nao eh isso que preciso, falei em
SCRIPT SHELL e nao a query, a query eu ja tenho:
kill_session.ksh
#!/bin/ksh export ORACLE_SID=xxxxexport ORACLE_HOME=/oracle/lalalaexport
PATH=$ORACLE_HOME/binsqlplus / as sysdba
@/home/oracle/xxxx/scripts/kill_session.sql
#kill_session.sql
SELECT p.spid FROM gv$session s, gv$process p WHERE s.paddr = p.addr AND
s.username IS NOT NULL AND s.status = 'KILLED';
Beleza, ele me gera um select dinamico com a query pra matar as sessoes, mas eu
preciso que isso seja feita de forma automatica, alguem pode ajudar?
Em Terça-feira, 12 de Dezembro de 2017 12:00, "Sérgio Luiz Rodrigues Chaves
sergio.cha...@elumini.com.br [oracle_br]" <oracle_br@yahoogrupos.com.br>
escreveu:
Bom dia,
O select abaixo é para identificação de locks mas pode ser adaptado para o que
você precisa. Atenção para o texto que está para ambiente de RAC.
SELECT gvh.inst_id INST_BLOQUEADORA, gvh.SID SID_BLOQUEADORA, gvs.serial#
SERIAL_BLOQUEADORA,gvs.status STATUS,gvs.username USUARIO_BLOQUEADOR, (select
Distinct substr(sql_text,0,999) from gv$sql where sql_id = gvs.PREV_SQL_ID)
SQL_BLOQUEADOR,gvs.module MODULO,gvs.CLIENT_INFO CLIENT_INFO, gvw.inst_id
INST_AGUARDANDO, gvw.SID SID_AGUARDANDO,(select distinct username from
gv$session where sid = gvw.sid and inst_id = gvw.inst_id)
USUARIO_AGUARDANDO,(select distinct substr(sql_text,0,999) from gv$sql where
sql_id in (select distinct sql_id from gv$session where sid = gvw.sid and
inst_id = gvw.inst_id)) SQL_AGUARDANDO,decode(gvh.type, 'MR',
'Media_recovery','RT', 'Redo_thread','UN', 'User_name','TX',
'Transaction','TM', 'Dml','UL', 'PLSQL User_lock','DX',
'Distrted_Transaxion','CF', 'Control_file','IS', 'Instance_state','FS',
'File_set','IR', 'Instance_recovery','ST', 'Diskspace Transaction','IV',
'Libcache_invalidation','LS', 'LogStaartORswitch','RW', 'Row_wait','SQ',
'Sequence_no','TE', 'Extend_table','TT', 'Temp_table','Nothing-')
TIPO_BLOQUEIO_ESPERA,decode(gvw.request, 0, 'None',1, 'NoLock',2,
'Row-Share',3, 'Row-Exclusive',4, 'Share-Table',5, 'Share-Row-Exclusive',6,
'Exclusive','Nothing-') modo_req_espera ,decode(gvs.username,'ATZ','----ATENCAO
ATZ',null)||'alter system kill session '|| '''' || gvh.SID || ',' ||
gvs.serial#||',@'||gvs.INST_ID|| '''
IMMEDIATE;---'||decode(gvs.username,'ATZ','ATENCAO ATZ',null)
"COMANDO_KILL_ORACLE",'EXEC KILL_SESSION ('|| gvh.SID || ',' ||
gvs.serial#||',' ||gvs.INST_ID||');' "COMANDO_KILL_ORACLE1",'kill -9 '||p.spid
"COMANDO_KILL_LINUX",LPAD(TRUNC(gvw.ctime/3600),6)||':'||LPAD(MOD(TRUNC(gvw.ctime/60),60),2,'0')||':'||LPAD(MOD(gvw.ctime,60),2,'0')
"TEMPO_ESPERA"FROM gv$lock gvh, gv$lock gvw, gv$session gvs, gv$process pWHERE
(gvh.id1, gvh.id2) in (SELECT id1, id2 FROM gv$lock WHERE
request=0INTERSECTSELECT id1, id2 FROM gv$lock WHERE lmode=0)AND
gvh.id1=gvw.id1AND gvh.id2=gvw.id2AND gvh.request=0AND gvw.lmode=0AND
gvh.SID=gvs.SIDAND gvh.inst_id=gvs.inst_idand gvs.paddr = p.addrand gvs.inst_id
= p.inst_id
Desde já agradeço. Sérgio Chaves.
De: oracle_br@yahoogrupos.com.br <oracle_br@yahoogrupos.com.br> em nome de
Rafael Mendonca raffaell.t...@yahoo.com [oracle_br]
<oracle_br@yahoogrupos.com.br>
Enviado: terça-feira, 12 de dezembro de 2017 11:01:48
Para: Yahoo! Brazil
Assunto: [oracle_br] AJuda script shell Pessoal, preciso de um script shell no
aix para matar *TODAS* as sessoes com status killed, no caso kill -9 pid:
exemplo:
SELECT p.spid FROM v$session s,
v$process p WHERE s.paddr = p.addr AND s.username IS
NOT NULL AND s.status = 'KILLED'/
Alguem poderia me ajudar?
#yiv0085736138 #yiv0085736138 -- #yiv0085736138ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0085736138
#yiv0085736138ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0085736138
#yiv0085736138ygrp-mkp #yiv0085736138hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv0085736138 #yiv0085736138ygrp-mkp #yiv0085736138ads
{margin-bottom:10px;}#yiv0085736138 #yiv0085736138ygrp-mkp .yiv0085736138ad
{padding:0 0;}#yiv0085736138 #yiv0085736138ygrp-mkp .yiv0085736138ad p
{margin:0;}#yiv0085736138 #yiv0085736138ygrp-mkp .yiv0085736138ad a
{color:#0000ff;text-decoration:none;}#yiv0085736138 #yiv0085736138ygrp-sponsor
#yiv0085736138ygrp-lc {font-family:Arial;}#yiv0085736138
#yiv0085736138ygrp-sponsor #yiv0085736138ygrp-lc #yiv0085736138hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0085736138
#yiv0085736138ygrp-sponsor #yiv0085736138ygrp-lc .yiv0085736138ad
{margin-bottom:10px;padding:0 0;}#yiv0085736138 #yiv0085736138actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0085736138
#yiv0085736138activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0085736138
#yiv0085736138activity span {font-weight:700;}#yiv0085736138
#yiv0085736138activity span:first-child
{text-transform:uppercase;}#yiv0085736138 #yiv0085736138activity span a
{color:#5085b6;text-decoration:none;}#yiv0085736138 #yiv0085736138activity span
span {color:#ff7900;}#yiv0085736138 #yiv0085736138activity span
.yiv0085736138underline {text-decoration:underline;}#yiv0085736138
.yiv0085736138attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv0085736138 .yiv0085736138attach div a
{text-decoration:none;}#yiv0085736138 .yiv0085736138attach img
{border:none;padding-right:5px;}#yiv0085736138 .yiv0085736138attach label
{display:block;margin-bottom:5px;}#yiv0085736138 .yiv0085736138attach label a
{text-decoration:none;}#yiv0085736138 blockquote {margin:0 0 0
4px;}#yiv0085736138 .yiv0085736138bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv0085736138
.yiv0085736138bold a {text-decoration:none;}#yiv0085736138 dd.yiv0085736138last
p a {font-family:Verdana;font-weight:700;}#yiv0085736138 dd.yiv0085736138last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0085736138
dd.yiv0085736138last p span.yiv0085736138yshortcuts
{margin-right:0;}#yiv0085736138 div.yiv0085736138attach-table div div a
{text-decoration:none;}#yiv0085736138 div.yiv0085736138attach-table
{width:400px;}#yiv0085736138 div.yiv0085736138file-title a, #yiv0085736138
div.yiv0085736138file-title a:active, #yiv0085736138
div.yiv0085736138file-title a:hover, #yiv0085736138 div.yiv0085736138file-title
a:visited {text-decoration:none;}#yiv0085736138 div.yiv0085736138photo-title a,
#yiv0085736138 div.yiv0085736138photo-title a:active, #yiv0085736138
div.yiv0085736138photo-title a:hover, #yiv0085736138
div.yiv0085736138photo-title a:visited {text-decoration:none;}#yiv0085736138
div#yiv0085736138ygrp-mlmsg #yiv0085736138ygrp-msg p a
span.yiv0085736138yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0085736138
.yiv0085736138green {color:#628c2a;}#yiv0085736138 .yiv0085736138MsoNormal
{margin:0 0 0 0;}#yiv0085736138 o {font-size:0;}#yiv0085736138
#yiv0085736138photos div {float:left;width:72px;}#yiv0085736138
#yiv0085736138photos div div {border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}#yiv0085736138
#yiv0085736138photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0085736138
#yiv0085736138reco-category {font-size:77%;}#yiv0085736138
#yiv0085736138reco-desc {font-size:77%;}#yiv0085736138 .yiv0085736138replbq
{margin:4px;}#yiv0085736138 #yiv0085736138ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv0085736138 #yiv0085736138ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0085736138
#yiv0085736138ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0085736138
#yiv0085736138ygrp-mlmsg select, #yiv0085736138 input, #yiv0085736138 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv0085736138
#yiv0085736138ygrp-mlmsg pre, #yiv0085736138 code {font:115%
monospace;}#yiv0085736138 #yiv0085736138ygrp-mlmsg *
{line-height:1.22em;}#yiv0085736138 #yiv0085736138ygrp-mlmsg #yiv0085736138logo
{padding-bottom:10px;}#yiv0085736138 #yiv0085736138ygrp-msg p a
{font-family:Verdana;}#yiv0085736138 #yiv0085736138ygrp-msg
p#yiv0085736138attach-count span {color:#1E66AE;font-weight:700;}#yiv0085736138
#yiv0085736138ygrp-reco #yiv0085736138reco-head
{color:#ff7900;font-weight:700;}#yiv0085736138 #yiv0085736138ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv0085736138 #yiv0085736138ygrp-sponsor
#yiv0085736138ov li a {font-size:130%;text-decoration:none;}#yiv0085736138
#yiv0085736138ygrp-sponsor #yiv0085736138ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv0085736138
#yiv0085736138ygrp-sponsor #yiv0085736138ov ul {margin:0;padding:0 0 0
8px;}#yiv0085736138 #yiv0085736138ygrp-text
{font-family:Georgia;}#yiv0085736138 #yiv0085736138ygrp-text p {margin:0 0 1em
0;}#yiv0085736138 #yiv0085736138ygrp-text tt {font-size:120%;}#yiv0085736138
#yiv0085736138ygrp-vital ul li:last-child {border-right:none
!important;}#yiv0085736138
