This SQL will be used to identify SQL details of given Session. You need to pass SID and INSTANCE_ID as INPUT. This query will provide session details, SQL_ID, SQL_EXEC_START time and other transaction level details.
set echo off feedback off verify off
col Transaction_id format a20
col username format a8
col days format 99.99
col hours1 format 999999
col TX_HRS format 999.9
col USER_HRS format 999.9
col LOCK format a2
column sql_address format a30
column sql_hash_value format 9999999999
column plan_hash_value format 9999999999999
accept v_instid prompt 'Enter Instance ID ->'
accept v_sid prompt 'Enter oracle sid ->'
set lines 168 pages 200
set long 3000
select a.inst_id || ':' || a.sid || ',' || a.serial# sid_info, a.service_name, a.sql_id xsql_id, a.sql_child_number xsql_child_number,
c.plan_hash_value, a.username, a.osuser, a.machine,
a.status sess_status, b.xidusn||'.'||b.xidslot||'.'||b.xidsqn Transaction_id, b.xid,
decode(UBAFIL,0, decode(UBABLK, 0, decode(UBASQN, 0, 'N', 'Y'), 'Y'), 'Y') "LOCK",
c.sql_profile, d.spid "os pid", a.program, a.module, c.sql_fulltext, a.event,
b.start_time "TX Start Time", round((sysdate - to_date(b.start_time, 'MM/DD/YY HH24:MI:SS')) * 24*60,1) "TX_MINS",
to_char(a.logon_time, 'DD-MON-RRRR:HH24:MI') "Logon Time", round((sysdate - a.logon_time) * 24*60,1) "LOGON_MINS",
a.blocking_session||'@'|| a.blocking_instance, a.sql_exec_start,
'alter system kill session ''' || a.sid || ',' || a.serial# || ', @' || a.inst_id || ''';' || chr(10) || lpad('+', 80, '+')
from gv$session a
left outer join gv$transaction b on (a.SADDR = b.SES_ADDR and a.inst_id = b.inst_id)
left outer join gv$sql c on (a.inst_id=c.inst_id and a.sql_id = c.sql_id and a.sql_child_number = c.child_number)
join gv$process d on (a.paddr = d.addr and a.inst_id=d.inst_id)
where a.sid='&v_sid'
and a.inst_id='&v_instid';