Measure Number of Active Sessions

--  measure number of Active Sessions

select
   round(sample_time,'MI'),
   count(*) as active_sessions
from
   dba_hist_active_sess_history
where
   sample_time > sysdate - 7
group by
   round(sample_time, 'MI');

Check Long Running operations

use following query for long operations with detail.




select v$session_longops.sid || ' : ' || v$session_longops.serial# "session",
       V$session.username || ' : ' || V$session.osuser "DB / OS USER",
       v$process.program,
       V$session.machine || ' | ' || V$session.terminal Machine,
       v$session_longops.opname || ' | ' || v$session_longops.target OPNAME_TARGET,
       v$session_longops.elapsed_seconds || ' | ' ||
       v$session_longops.time_remaining Progress,
       v$process.spid,
       round(v$process.pga_used_mem / 1024 / 1024) || '  |  ' ||
       round(v$process.pga_alloc_mem / 1024 / 1024) "PGA USE | ALLOC",
       v$sql.sql_text,
       v$sql.sorts,
       v$sql.executions,
       v$sql.disk_reads,
       v$sql.parse_calls,
       v$sql.service,
       v$sql.module
  from v$session_longops, V$process, V$sql, V$session
 where v$session.sid = v$session_longops.sid
   and v$process.addr = v$session.paddr
   and v$session.sql_id = v$sql.sql_id
   and V$session_longops.time_remaining > 0;