ACTIVE SESSION DETAILS SCRIPT IN ORACLE DATABASE

irut@/home/oracle/dba/scripts: cat active_session_history.sql

spool active_session_history.log

set pages 5000

set lines 500

set trims on

col sample_time format a25

col event format a30

col sql_id format a15

col sql_text format a200

col session_id format 99999

col p1text format a10

col p2text format a30

select sh.inst_id,

       sh.sample_time,

       sh.sql_id,

       sh.session_id,

       sh.blocking_session,

       sh.wait_time,

       sh.time_waited,

       sh.event,

       sh.p1,

       sh.p1text,

       sh.p2,

       sh.p2text,

       (select distinct substr(t.sql_text,1,200)

        from gv$sqlarea t

        where sh.sql_id = t.sql_id) sql_text

from gv$active_session_history sh

where sh.event is not null

--and sh.sample_time > sysdate-10/1440

and sh.session_id=&SID

order by sh.sample_time,sample_id;

spool off;