**********************************************************************
* Description: SQL for Quering Blocking Session and Resolve Procedure
* Compatiablity: RDBMS 10g, 11g, 12c
* Date: 14:35 PM EST, 01/08/2017
**********************************************************************


<1> Method 1 - SQL to detect blocking and blocked sessions:
    |
    |__ SQL> SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status 
             FROM v$lock l1, v$session s1, v$lock l2, v$session s2 
             WHERE s1.sid=l1.sid AND s2.sid=l2.sid 
             AND l1.BLOCK=1 AND l2.request > 0 
             AND l1.id1 = l2.id1 
             AND l1.id2 = l2.id2;
	
	
===========================================================	


<2> Method 2 - SQL to detect blocking and blocked sessions:
    |
    |__ SQL> SET LINESIZE 800
             select '<>:',a.username as blockedUser, '<>:', a.sid AS blockedSID, '<>:', a.event as blockedEvent,
             '<>:', a.sql_id as blockingSQLID, '<>: ',a.prev_sql_id as blockedPrevSQLID,
             '<>:',a.seconds_in_wait as blockedSecondsWait, '<>:',b.sid as blockingSID, '<>:', b.sql_id as blockingSQLID,
             '<>: ',b.prev_sql_id as blockingPrevSQLID, nvl2(b.sql_id,'BLOCKINGSQLIDISNOTNULL','BLOCKINGSQLIDISNULL')
             FROM GV$session a LEFT OUTER JOIN GV$session b ON b.sid=a.blocking_session AND b.inst_id = a.blocking_instance
             WHERE  a.username is not null
             and a.status='ACTIVE'
             and a.event not like '%PX%'
             and a.event not like '%SQL%'
             and a.event not like '%Stream%'
             and a.event not like '%broadcast%'
             and a.event not like '%DIAG%'
             and a.event not like '%RMAN backup%'
             and a.seconds_in_wait > 120;
		 
		 
===========================================================


<3> Querying full SQL text by blocking sql_id:	
    |
    |__ SQL> set long 9999;
             select sql_id from v$session where sid=;	
             select sql_text from v$sql where sql_id='gpvhyhb9ju91f';

		 
===========================================================		 

		 
<4> Fetching blocking session detail by sid:		 
    |
    |__ SQL> select SID,SERIAL#,USERNAME,MACHINE,PROGRAM,SQL_ID from v$session where SID=433;

	
===========================================================


<5> SQL to kill the session:
    |
    |__ SQL> alter system kill session 'sid, serial#';
             select 'alter system kill session '||''''||sid||','||serial#||''''||' immediate;' from v$session where username='TEST' and status='ACTIVE';