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;
