How to remove a “killed“ session that stays for a long time(days)

缺乏、安全感 2023-10-05 16:00 135阅读 0赞

-- How to remove a “killed” session that stays for a long time(days)

-- No Responsible for the following posted script. User should test it before apply it to production

-- check the “killed” sessions

select inst_id, SID, SERIAL#, USERNAME, PADDR, LOGON_TIME, STATUS, SERVER, OSUSER

from gv$session where status=’KILLED’

/

-- check sessions without processes associated

select spid, program from v$process

where program!= ‘PSEUDO’

and instr(program,’(‘) < 1

and addr not in (select paddr from v$session)

and addr not in (select paddr from v$bgprocess)

and addr not in (select paddr from v$shared_server);

-- Find the associated PID: Match (close to the same date) the killed session(logon time) and the processes(timestamped) from above output

!ps -ef|egrep “4340|21873|8330|8332|21559|4342|4063|4338|4336|8328|17102|17098” |grep -v grep

-- Cross Check - there should have no PID associated with the killed session

SELECT p.spid oracle_dedicated_process, s.process clientpid, s.sid FROM v$process p, v$session s

WHERE p.addr = s.paddr AND s.sid= &sid

-- Cross check - there should have no SID associated with the matched PID

SELECT p.spid oracle_dedicated_process, s.process clientpid, s.sid

FROM v$process p, v$session s WHERE p.addr = s.paddr

AND p.spid=&PID;

-- Kill the PID

ps -ef|grep

kill -9

-- The killed PID should not be shown in the following output

select spid, program from v$process

where program!= ‘PSEUDO’

and addr not in (select paddr from v$session)

and addr not in (select paddr from v$bgprocess)

and addr not in (select paddr from v$shared_server);

-- One of the killed sessions(matched the same date) should be gone from the following output

select SID, SERIAL#, USERNAME, PADDR, LOGON_TIME, STATUS, SERVER, OSUSER

from v$session where status=’KILLED’

/

发表评论

表情：
评论列表 （有 0 条评论，135人围观）

还没有评论，来说两句吧...

相关阅读