Tuesday, January 29, 2013

check the root blocker and kill it

Today On OTN this question has been asked how to check the root killer and kill it, While searching and browsing i found nice article i would love to share written by Moid I would thank him for his sharing and amazing topics

Step #1 : 
Create a view called root_blocker, and Grant read only permission to  public

Create or replace view root_blocker
as
    (select
       to_char(a.logon_time, 'MM/DD/YY HH:MI:SS') as Logon_Time,
       a.inst_id,
       b.username||'@'||a.machine as blocker,
       a.sql_id,
       c.sql_text,
       '('||a.sid||','||a.serial#||')' as root_blockers_sid_n_serial#,
       'ALTER SYSTEM KILL SESSION '''|||a.sid||','||a.serial#||''' IMMEDIATE;' as "10g Command to kill session",
       'ALTER SYSTEM KILL SESSION '''||a.sid||','||a.serial#||'@'||a.inst_id||''' IMMEDIATE;' as "11g Command to kill session"
    from
       gv$session a,
       dba_users b,
       gv$sqltext c
    where
       b.username=a.username
       and a.sql_id=c.sql_id
       and logon_time=(select
                       max(logon_time)
                   from
                       V$session
                   where blocking_session_status='VALID')
    );
 Step #2:
Now, using the help of plan_table, let’s display the values vertically so that it is easier for us to read the output.

set linesize 300
SET SERVEROUTPUT ON FORMAT WRAPPED
exec print_table( 'select * from sys.root_blocker' );

SQL> exec print_table('select * from sys.root_blocker');
LOGON_TIME                    : 09/25/10 01:17:37
INST_ID                       : 1
USERNAME                      : SCOTT
SQL_ID                        : 4t5dk4yfn6gx8
SQL_TEXT                      :  update t11 set sal=2001 where empno=7521
ROOT_BLOCKERS_SID_N_SERIAL#   : (73,22025)
 and you could kill it like the below screen shot :





Step #3:
To check the complete list of locked_session/blockers, use the following query.

select
    to_char(a.logon_time, 'MM/DD/YY HH:MI:SS') as Logon_Time,
    a.inst_id,
    b.username||'@'||a.machine as blocker,
    a.sql_id,
    c.sql_text,
    '('||a.sid||','||a.serial#||')' as root_blockers_sid_n_serial#,
    'ALTER SYSTEM KILL SESSION '||a.sid||','||a.serial#||''' IMMEDIATE;' as "10g Command to kill session",
    'ALTER SYSTEM KILL SESSION '''||a.sid||','||a.serial#||'@'||a.inst_id||''' IMMEDIATE;' as "11g Command to kill session"
from
    gv$session a,
    dba_users b,
    gv$sqltext c
where
    b.username=a.username
    and a.sql_id=c.sql_id
    and a.blocking_session_status='VALID'
order by logon_time desc;

Original Post Here

Thank you

No comments:

Post a Comment