SELECT
  I1.INSTANCE_NAME AS "INST", S.SID, S.SERIAL#
, 'ALTER SYSTEM KILL SESSION ''' || S.SID || ', ' || S.SERIAL# || ', @' || S.INST_ID || ''' IMMEDIATE;' AS "KILLSQL"
, S.BLOCKING_SESSION_STATUS
, I2.INSTANCE_NAME AS "BLOCKING_INST", S.BLOCKING_SESSION AS "BLOCKING_SID"
, S.FINAL_BLOCKING_SESSION_STATUS
, I3.INSTANCE_NAME AS "FINAL_INST", S.FINAL_BLOCKING_SESSION AS "FINAL_SID"
, MAX(L.CTIME) AS "CTIME"
, S.STATUS
, S.SQL_EXEC_START
, S.OSUSER, S.MACHINE, S.PROGRAM, S.SQL_ID
, T.SQL_TEXT
, S.PREV_SQL_ID
, T2.SQL_TEXT AS "PREV_SQL_TEXT"
FROM GV$SESSION S
INNER JOIN GV$LOCK L
  ON L.INST_ID = S.INST_ID
 AND L.SID = S.SID
 AND L.TYPE IN ('TM', 'TX')
LEFT JOIN GV$INSTANCE I1
  ON I1.INST_ID = S.INST_ID
LEFT JOIN GV$INSTANCE I2
  ON I2.INST_ID = S.BLOCKING_INSTANCE
LEFT JOIN GV$INSTANCE I3
  ON I3.INST_ID = S.FINAL_BLOCKING_INSTANCE
LEFT JOIN GV$SQLSTATS T
  ON T.INST_ID = S.INST_ID
 AND T.SQL_ID = S.SQL_ID
LEFT JOIN GV$SQLSTATS T2
  ON T2.INST_ID = S.INST_ID
 AND T2.SQL_ID = S.PREV_SQL_ID
WHERE S.TYPE != 'BACKGROUND'
GROUP BY I1.INSTANCE_NAME, S.INST_ID, S.SID, S.SERIAL#, S.BLOCKING_SESSION_STATUS, I2.INSTANCE_NAME, S.BLOCKING_SESSION, S.FINAL_BLOCKING_SESSION_STATUS, I3.INSTANCE_NAME , S.FINAL_BLOCKING_SESSION, S.LOCKWAIT, S.OSUSER, S.MACHINE, S.PROGRAM, S.SQL_ID, T.SQL_TEXT, S.PREV_SQL_ID, T2.SQL_TEXT, S.STATUS, S.SQL_EXEC_START
--ORDER BY FINAL_INST, FINAL_SID, BLOCKING_INST, BLOCKING_SID, INST, SID
--ORDER BY FINAL_INST, FINAL_SID, BLOCKING_INST, BLOCKING_SID, MACHINE, PROGRAM, INST, SID
ORDER BY FINAL_INST, FINAL_SID, BLOCKING_INST, BLOCKING_SID, MACHINE, CTIME DESC, PROGRAM, INST, SID
--ORDER BY FINAL_INST, FINAL_SID, BLOCKING_INST, BLOCKING_SID, CTIME DESC, INST, SID