set lines 300
set pages 50000
col username format a12
col sid format 999999
col inst_id format 99999
col sql_text format a36
col status format a8
SELECT 
	f.INST_ID, 
	f.sid, 
	f.username, 
	f.STATUS, 
	f.BLOCKING_INSTANCE, 
	f.blocking_session, 
	f.lockwait, 
	f.SECONDS_IN_WAIT, 
	f.LAST_CALL_ET, 
	s.SQL_TEXT, 
	s.hash_value
  FROM (SELECT 
			INST_ID, 
			sid, 
			username, 
			STATUS, 
			BLOCKING_INSTANCE,
			blocking_session, 
			lockwait, 
			SECONDS_IN_WAIT, 
			LAST_CALL_ET, 
			SQL_HASH_VALUE
         FROM gv$session WHERE blocking_session is not null and SECONDS_IN_WAIT > 0
         UNION ALL
        SELECT 
			A.INST_ID,
			A.sid, 
			A.username, 
			A.STATUS, 
			A.BLOCKING_INSTANCE, 
			A.blocking_session, 
			A.lockwait, 
			A.SECONDS_IN_WAIT, 
			A.LAST_CALL_ET, 
			A.SQL_HASH_VALUE
       FROM gv$session A, gv$session B
       WHERE 
			A.sid = B.blocking_session 
		AND 
			A.INST_ID = b.BLOCKING_INSTANCE) f,
       gv$sqlarea s
   WHERE 
	f.inst_id = s.inst_id and f.sql_hash_value = s.hash_value;