Show all running SQLs

select distinct spid,
       s.sid,
       s.serial#,to_char(sysdate - last_call_et/(24*3600),'mm/dd/yy
hh24:mi:ss') "LAST_ACTIVITY",
       logon_time,
       osuser,
       s.program,
       schemaname,
       sql_text
  from v$session s,
       v$process p,
       v$sql t
  where s.paddr=p.addr
    and t.hash_value(+)=s.sql_hash_value
    and s.type !='BACKGROUND';

-------------------------------------------------------------------------------------------------------------

 select status , sid from v$session where module like '%b6226547.sql%';

-------------------------------------------------------------------------------------------------------------

SQL> select s.username, s.sid, t.sql_text "Last SQL"

from gv$session s, gv$sqlarea t

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

s.sid = '&sid';

-------------------------------------------------------------------------------------------------------------

set echo off feedback off heading off

set pagesize 0

set linesize 180

col username for a25

col osuser for a12

col serial# for 9999999

col sid for 9999

col spid for a8

col module for a10 trunc

col start_time for a20

col machine for a20 trunc

select 'Show users ordered by logon_time, username' from dual;

select 'OSUSER       OSPID    USERNAME                  SID   SERIAL#  LOGON_TIME             STATUS MACHINE              MODULE' from dual;

select '------------ -------- ------------------------  ----- -------- ------------------- --------- -------------------- ----------' from dual;

select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,'Dy dd Mon HH24:MI:SS') start_time,s.status,s.machine,s.MODULE 

from V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null order by logon_time,1;

-------------------------------------------------------------------------------------------------------------

Monitor Session Activity