Kill -9 tiến trình gây cao tải cơ sở dữ liệu trong Oracle Database

/***************************************** KILL ******************************************/
-- Xac dinh process tu inst_id, status, username, sql_id, machine, event,
SELECT /*username*/  'kill -9 ' || SPID A ,a.INST_ID,A.SID,A.SQL_ID, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND, A.EVENT
FROM gv$session a, gv$process b 
WHERE b.ADDR = a.paddr
AND a.inst_id=b.inst_id 
--AND B.inst_id = 4
--and a.status='INACTIVE'
--and A.USERNAME LIKE 'SYS'
--AND A.USERNAME not  in ('SYS','GGATE','GOLDENGATE',''ORA_RECO_070361')
--and a.username not in ('SYS','SYSTEM','SYSMAN','BINHTV','DBSNMP','GGATE','GOLDENGATE')
--AND a.program LIKE '%rman%'
--AND sql_id in ('gbmyfjjdcyk75')
--and machine  like '%machine%'
and a.event in  ('library cache lock','library cache load lock','library cache: mutex X','cursor: pin S wait on X','library cache pin','gc buffer busy acquire','enq: TS - contention','enq: TX - row lock contention','enq: TM - contention','db file parallel read','row cache lock','enq: DX - contention','enq: US - contention')
--and  round(to_number(sysdate-a.prev_exec_start)*1440) >30 
and type='USER'
order by a.inst_id;


SELECT /*SID*/  'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,a.prev_exec_start,BACKGROUND
FROM gv$session a, gv$process b
WHERE b.addr = a.paddr 
AND a.inst_id=b.inst_id
--and b.inst_id=2
AND a.sid in (
4588
)
and type='USER'
and machine  not like '%BINHTV%' --and user not like 'SYS'
order by inst_id;
 
SELECT /*call package*/  'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,a.prev_exec_start,BACKGROUND
FROM gv$session a, gv$process b
WHERE b.addr = a.paddr 
AND a.inst_id=b.inst_id
--and b.inst_id=4
AND (b.inst_id, a.sid) in (
(select /*+ parallel(8) */  inst_id, sid from gv$access where object like '%APP%')
)
and type='USER'
and a.machine not like '%BINHTV%' ;

SELECT /*lock table*/  'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND
FROM gv$session a, gv$process b
WHERE b.addr = a.paddr 
AND a.inst_id=b.inst_id
--and b.inst_id=3
AND (b.inst_id, a.sid) in
(SELECT /*+ parallel(8)*/ s.inst_id,s.sid
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
and object_name=upper('SYS_SUB'))
--and type='USER'
--ORDER BY username, session_id;

select /*+ parallel(8) */ sid||',' from gv$access where upper(object) like upper('TEST_TABLE')
and inst_id=1;

-- Xac dinh user dang chay cau lenh SQL nao
select  p.INST_ID, 'kill -9 '||P.SPID SPID, s.SID, s.username su, substr(sa.sql_text,1,540) SQL_TEXT
from gv$process p,gv$session s,gv$sqlarea sa
where p.addr=s.paddr and p.INST_ID=s.INST_ID and s.username is not null and s.sql_address=sa.address(+) and s.sql_hash_value=sa.hash_value(+)
and s.username=upper('BINHTV')
and type='USER'
order by INST_ID, SID;

--alter system kill session '1066,21548,@1' immediate; /*SID, Serial#*/
--alter system kill session '1921,46696' immediate;

select *--'alter system kill session ' || '''' || sid || ',' || SERIAL# || '''' || ' immediate;'
from gv$session
where event = 'enq: TX - row lock contention'
or event like '%lock contention%';

select 'alter system kill session ' || '''' || sid || ',' || SERIAL# || '''' || ';',TIME_REMAINING+ELAPSED_SECONDS,TIME_REMAINING,TARGET
from v$session_longops a where TIME_REMAINING>0
order by TIME_REMAINING+ELAPSED_SECONDS;


@ Trần Văn Bình - Founder of Oracle DBA AZ
#học oracle #oracle database #khóa học oracle online #khóa học oca #học oca ở đâu #oca là gì #oca oracle #BossData #OraAz #OracleDBAAz #OracleTutorial #Quản_trị_cơ_sở_dữ_liệu_Oracle #OracleDBA #OracleDatabaseAdministration