* LongOps.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
col UNAM2 format a20  word  heading "Oracle User/OS User"
col PROG format a15 word heading "Program|Terminal"
col SID  format a10 word heading "SID/|Serial#"
col STAT format A7 Heading "Status"
col LTIM format a20 word heading "Logon Time/|Last Activy time"
col TIMI format a20 word heading "Start time/|Last Update time|remaining->elapsed|[%done end time]"
col STMT format a64 heading "Statement" WORD
col OPNA format a10 WORD PRINT HEADING "Long Op"
col WORK format a12 WORD PRINT HEADING "Target|So far|Total Work"
col MESS format a100 WORD PRINT HEADING "Long op message" NEWLINE
SELECT	 lo.opname opna
	,NVL(ses.username,lo.username)||' / '||ses.osuser||DECODE(ses.audsid,userenv('SESSIONID'),'**','')  unam2
	,NVL(ses.status,'unknown') stat
--	,TO_CHAR(ses.logon_time,'DD-MON-YYYY:HH24:MI:SS')||'  '
--        ||to_char(sysdate - (ses.last_call_et)/86400,'DD-MON-YYYY:hh24:mi:ss') ltim
	,REPLACE(s.sql_text,CHR(10),' ') stmt
	 ||LPAD(TO_CHAR(totalwork),11,'_')||' ('||units||') [' 
	,start_time||' '
	 ||' '||TO_CHAR(time_remaining)||''
	 ||'->'||elapsed_seconds||' ['||
	 DECODE(0,time_remaining+elapsed_seconds,'n/a',CEIL(100*elapsed_seconds/(time_remaining+elapsed_seconds)))||'% eta ' 
	 ||TO_CHAR(sysdate+(time_remaining/(60*60*24)),'HH24:MIpm')||']' timi
	,DECODE(ses.program,'','',ses.program||' '||ses.terminal)  prog
--	||'('||RTRIM(ses.machine)||')'
	,lo.sid||'/'||lo.serial#||' ['||ses.audsid||']' sid
	,message mess
FROM	 v$sql s
	,v$session_longops lo
	,v$session ses
WHERE	lo.sql_address=s.address
AND	lo.sql_hash_value=s.hash_value 
AND	lo.time_remaining!=0
AND	lo.sid=ses.sid(+)
AND	lo.serial#=ses.serial#(+)

set doc off
/* SUMMARY - Show long running SQL details, progress and estimated time to complete
 * DETAIL - Oracle will record metrics of statements it deems to be "long-running" - ie it expects to take
 *          considerable time to execute. This typically means it has detected that the execution path will use
 *          full tables scans and/or hash joins with a large number of blocks scanned. The data is exposed via
 *          the V$SESSION_LONGOPS view. Processes may also write directly to this view via the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS
 *          procedure - something that RMAN makes use of as can any application code, if required. This makes 
 *          DBMS_APPLICATION_INFO a very useful package indeed for instrumenting code - you can create a row in V$SESSION_LONGOPS when
 *          your process starts and update it at intervals so the progress can be monitored by some other external process.
 *          Oracle documentation states: "This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution."
 *          In 10g rollback data was added too.
 *          Data is kept in the view for a certain length of time (basically it will be re-used as space is needed) so it
 *          is also a very useful place to look for data after the event - it can show you things like how many block visits
 *          were made, and how long the statement actually took.
 *          The script given here uses V$SESSION_LONGOPS to locate statements that are still running and gives details of 
 *          how much work has been done, how long it has so far taken, and when Oracle expects the operation to complete. It joins back to V$SESSION and V$SQL to give details
 *          of who and what is being run.
 *          To see statements that have already completed remove the line "AND lo.time_remaining!=0" (always assuming the required data, SQL as well as LONG_OPS is still available).
                                                                              Start time/
                                                                   Target     Last Update time
           Oracle User/                                            So far     remaining->elapsed   Program  SID/
Long Op    OS User      Status   Statement                         Total Work [%done end time]     Terminal Serial#
---------- ------------ -------- --------------------------------- ---------- -------------------- -------- -------
Long op message
Sort       TONIONS /    INACTIVE select from bobs j1,nobs j2  ________, 04-MAY-2007 16:53:15 SQLPLUS  288/557
Output     tonions                                                  _____455, 04-MAY-2007 16:53:33 MyPc     [733977]
                                                                    ____3794  132->18 [12% eta     
                                                                    (Blocks)  16:55pm]             
Sort Output:  : 455 out of 3794 Blocks done
Table Scan TONIONS /    unknown INSERT INTO mjh (SELECT id FROM bb) TONIONS., 02-MAY-2007 07:27:03           271/4382
           tonions                                                  __193000, 02-MAY-2007 07:30:15           []
                                                                    __193006  0->192 [99% eta
                                                                    (Blocks)  16:53pm]
Table Scan:  TONIONS.BOB: 193000 out of 193006 Blocks done