×
1. 게시판에 자주 글을 남긴다.
2. [ Oracle Database ] 기술문서 게시판에 반드시 주 1회 이상 셀프 스터디한 내용을 올린다.
3. 타인의 질문에 적극적으로 답변한다.
1. 게시판에 자주 글을 남긴다.
2. [ Oracle Database ] 기술문서 게시판에 반드시 주 1회 이상 셀프 스터디한 내용을 올린다.
3. 타인의 질문에 적극적으로 답변한다.
DB 세션 Auto Kill Script
-
ylq123
(ylq123) - 이 글의 작성자
- Offline
- Newbie
덜보기
더보기
- Posts: 5
- Thank you received: 0
29 Nov 2021 17:56 - 18 Jan 2022 17:02 #5419
작성자: ylq123
ylq123 님의 글: DB 세션 Auto Kill Script
sqlplus "/as sysdba" << !
set serveroutput on
alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
column dcol new_value dbname noprint
select INSTANCE_NAME DCOL from V\$INSTANCE;
set heading off
spool /SCRIPT/DB/KILL/lock_kill_&dbname..log
select 'Process:'||D.SPID
from V\$LOCK A, V\$SESSION_WAIT B, V\$SESSION C, V\$PROCESS D,
(select SID, max(CTIME) from V\$LOCK where (ID1, ID2, type) in (select ID1, ID2, type from V\$LOCK where REQUEST > 0) group by SID) E
where (A.ID1, A.ID2, A.type) in (select ID1, ID2, type from V\$LOCK where REQUEST > 0)
and A.CTIME > 1800
and A.SID = B.SID
and A.SID = C.SID
and A.SID = E.SID
and A.REQUEST = 0
and A.TYPE = 'TX'
and C.PADDR = D.ADDR
and B.EVENT = 'SQL*Net message from client'
and C.USERNAME not in ('SYS', 'SYSTEM')
and C.STATUS = 'INACTIVE';
spool off
declare
KILL_SQL varchar2(100);
STATUS_SQL varchar2(100);
cursor GREP_SESSION is
select A.SID, C.SERIAL#, D.SPID, C.USERNAME, C.OSUSER, C.MACHINE, C.PROGRAM, A.CTIME, C.LOGON_TIME, B.EVENT
from V\$LOCK A, V\$SESSION_WAIT B, V\$SESSION C, V\$PROCESS D,
(select SID, max(CTIME) from V\$LOCK where (ID1, ID2, type) in (select ID1, ID2, type from V\$LOCK where REQUEST > 0) group by SID) E
where (A.ID1, A.ID2, A.type) in (select ID1, ID2, type from V\$LOCK where REQUEST > 0)
and A.CTIME > 1800
and A.SID = B.SID
and A.SID = C.SID
and A.SID = E.SID
and A.REQUEST = 0
and A.TYPE = 'TX'
and C.PADDR = D.ADDR
and B.EVENT = 'SQL*Net message from client'
and C.USERNAME not in ('SYS','SYSTEM')
and C.STATUS = 'INACTIVE';
begin
dbms_output.enable(1000000);
for R_TABS in GREP_SESSION
loop
KILL_SQL := 'alter system kill session ' || CHR(39) || '' || R_TABS.SID || ',' || R_TABS.SERIAL# || '' || CHR(39);
DBMS_OUTPUT.PUT_LINE( CHR(10) );
DBMS_OUTPUT.PUT_LINE( ' * Current Time : ' || SYSDATE );
DBMS_OUTPUT.PUT_LINE( ' * Holder Logon Time : ' || R_TABS.LOGON_TIME );
DBMS_OUTPUT.PUT_LINE( ' * SQL Execution Time : ' || ROUND(R_TABS.CTIME/60,2) || ' (Min)' );
DBMS_OUTPUT.PUT_LINE( ' * OS Process : ' || R_TABS.SPID );
DBMS_OUTPUT.PUT_LINE( ' * DB Session : ' || R_TABS.SID );
DBMS_OUTPUT.PUT_LINE( ' * Serial# : ' || R_TABS.SERIAL# );
DBMS_OUTPUT.PUT_LINE( ' * OS User : ' || R_TABS.OSUSER );
DBMS_OUTPUT.PUT_LINE( ' * DB Username : ' || R_TABS.USERNAME );
DBMS_OUTPUT.PUT_LINE( ' * Machine : ' || R_TABS.MACHINE );
DBMS_OUTPUT.PUT_LINE( ' * Program : ' || R_TABS.PROGRAM );
DBMS_OUTPUT.PUT_LINE( ' * Holder Wait Event : ' || R_TABS.EVENT );
DBMS_OUTPUT.PUT_LINE( CHR(10) );
DBMS_OUTPUT.PUT_LINE(KILL_SQL);
execute immediate KILL_SQL;
select STATUS into STATUS_SQL from V\$SESSION where SID = R_TABS.SID;
DBMS_OUTPUT.PUT_LINE( CHR(10) );
DBMS_OUTPUT.PUT_LINE( ' * Current Time : ' || SYSDATE );
DBMS_OUTPUT.PUT_LINE( ' * Status after Kill : ' || STATUS_SQL );
DBMS_OUTPUT.PUT_LINE( CHR(10) );
end loop;
end;
/
!
cd /SCRIPT/DB/KILL
process=`cat lock_kill_${ORACLE_SID}.log | grep '^Process:' | grep -v ora_ | cut -d : -f2`
confirm_process=`ps -ef | grep oracle | grep ${ORACLE_SID} | grep $process`
if [ $? -eq 0 ]
then
echo '\nProcess $process will be killed ... \n'
kill -9 $process
fi
rm /SCRIPT/DB/KILL/lock_kill_${ORACLE_SID}.log
Time to create page: 0.052 seconds
- 현재 위치:
- 홈
- Forum
- Sarc Study Group
- Oracle Database
- DB 세션 Auto Kill Script