BLOCKED & BLOCKING SESSIONS


/* 10g - BLOCKED & BLOCKING SESSIONS*/

SELECT   sid Blocked_session,
         blocking_session,
         blocking_session_status block_status,
         v$session.username,
         event,
         seconds_in_wait,module,program,machine
  FROM   v$session
WHERE   blocking_session IS NOT NULL ;

SELECT 
         blocking_session,count(*)      
  FROM   v$session
WHERE   blocking_session IS NOT NULL   group by blocking_session order by count(*) desc;

 SELECT   sid Blocked_session,
         blocking_session,
         blocking_session_status block_status,
         v$session.username,
         event,
         seconds_in_wait
  FROM   v$session
WHERE   blocking_session IS NOT NULL and event='enq: TX - row lock contention';

SELECT   v$session.sid,
         spid,
         v$session.username,
         status,
         v$session.machine,
         v$session.program,v$session.event,
         sql_text
  FROM   v$session, v$process, v$sql
WHERE   v$process.addr = v$session.paddr
         AND v$session.sql_hash_value = v$sql.hash_value(+) and v$session.paddr = v$process.addr and v$session.type <> 'BACKGROUND'
and v$session.sid IN
                  (SELECT  distinct c.blocking_session
                     FROM   v$session c
                    WHERE   blocking_session IS NOT NULL
                    --and event in ('enq: TX - row lock contention')
                    --and seconds_in_wait > 20 
                     );

/* 10g- KILL BLOCKING SESSIONS*/

SELECT   'kill -9 ' || b.spid
  FROM   v$session a, v$process b
WHERE   a.paddr = b.addr and a.type <> 'BACKGROUND'
         AND a.sid IN
                  (SELECT  distinct c.blocking_session
                     FROM   v$session c
                    WHERE   blocking_session IS NOT NULL
                    --and event in ('enq: TX - row lock contention')
                    --and seconds_in_wait > 20 
                     );

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: