--tab=SQL Monitor
select replace(dbms_sql_monitor.report_sql_monitor(sql_id => m.sql_id, session_id => m.sid, session_serial => m.session_serial#, sql_exec_id => m.sql_exec_id, sql_exec_start => m.sql_exec_start, type => 'ACTIVE', report_level => 'ALL' ), 'http:','https:') as report, 
       m.sql_id, m.sql_exec_id, m.sql_plan_hash_value 
     , m.sql_exec_start --, m.first_refresh_time 
     , m.last_refresh_time 
     , m.status 
     , cast(numtodsinterval(m.last_refresh_time - m.sql_exec_start,'DAY') as interval day(1) to second(0)) as monitor_elapsed  -- Time calculated from last_refresh_time - last_refresh_time 
     , rpad('|', 100* ratio_to_report(m.last_refresh_time - m.sql_exec_start) over (), '|') as monitor_elapsed_graph 
     --, cast(numtodsinterval(m.elapsed_time/1e6,'SECOND') as interval day(1) to second(0)) as elapsed_time  -- V$SQL_MONITOR.ELAPSED_TIME: Elapsed time (in microseconds); updated as the statement executes 
     --, rpad('|', 100* ratio_to_report(m.elapsed_time) over (), '|') as elapsed_time_graph 
     --, cast(numtodsinterval(m.cpu_time/1e6,'SECOND') as interval day(1) to second(0)) as elapsed_cpu 
     --, m.buffer_gets 
     , o.object_name as plsql_unit 
     , s.program_line# as plsql_line# 
     , m.sql_text 
from   v$sql_monitor m 
       left join v$sql s on s.sql_id = m.sql_id and s.child_address = m.sql_child_address 
       left join all_objects o on o.object_id = s.program_id 
       --join v$session s on s.sid = m.sid and s.serial# = m.session_serial#  -- Currently active sessions only
where  (m.sid, m.session_serial#) in
       ((&< name="SID, Serial#" description="yes" list="select distinct 
               nvl2(px.qcsid, px.qcsid||', '||nvl(px.qcserial#,px.serial#), se.sid||','||se.serial#) 
             , nvl2(px.qcsid, px.qcsid||', '||nvl(px.qcserial#,px.serial#), se.sid||','||se.serial#)||'  '||initcap(u.firstname||' '||u.lastname) 
        from   v$session se 
               join v$sql_monitor mm on mm.sid = se.sid and mm.session_serial# = se.serial# 
               left join v$px_session px on px.sid = se.sid 
               left join app_users u on u.username = se.osuser 
        where  se.type = 'USER' and se.status = 'ACTIVE' and se.sql_id is not null 
        and    se.audsid != sys_context('userenv','sessionid') 
        and    se.osuser <> 'oracle' 
        and    se.sql_id is not null order by 1">)) 
--and    lower(substr(ltrim(m.sql_text),1,5)) not in ('begin','decla')  -- exclude top-level calling block 
and    not regexp_like(m.sql_text, '^\s*(begin|declare)', 'i') 
order by m.sql_exec_start desc;

-- Or if you know the SQL ID:
select replace(dbms_sql_monitor.report_sql_monitor 
       ( sql_id => '&sql_id', type => 'ACTIVE', report_level => 'ALL' ) 
       , 'http:','https:') as report 
from dual;
