Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: V$SESSION_LONGOPS


From: Wolfson Larry - lwolfs <>
Date: Fri, 10 Sep 2004 18:01:53 -0500
Message-ID: <>

Got the following from Metalink. So I don't see something because "** Only considered a 'Longop' if the table exceeds 10,000 blocks formatted blocks."
OK maybe. Thought they were more than that but I could be wrong. Throwing out 6 second rule.  

    Thanks, Jacques,  


From: Claudia <> O'Callaghan 05-Mar-03 04:32
Subject: Re : Queries running longer than 15 minutes

You can't rely on V$SESSION_LONGOPS as not all long running operations get logged there. You can try the following

select a.username,a.sid,a.status,a.last_call_et,b.sql_text from v$session a, v$sqlarea b, v$process c where a.sql_address=b.address and a.sql_hash_value=b.hash_value

and a.paddr=c.addr 
and a.username is not null 
and a.last_call_et>(5*60) 
and a.status='ACTIVE'; 

where last_call_et is the number of seconds since the call began. (Note: it is not updated continuously but is close enough)


Subject: 	Oracle8i: User-definable V$SESSION_LONGOPS Entries	
Content Type: 	TEXT/PLAIN	
Creation Date: 	10-FEB-1999	
Last Revision Date: 	05-MAY-2004	

Oracle8i User definable V$SESSION_LONGOPS Entries


Oracle8i V$SESSION_LONGOPS entries can be maintained by users.  

In Oracle 8.0, the view V$Session_longops was introduced to allow

Server processes to indicate some idea of how far they had progressed

(specifically RMAN).  

In Oracle8i, this list was expanded to include:  

Archiving                      *  

Rman Backup and Restore

Parallel Query

Recovery ( Crash and Media )

Full Table scans **


Analyze using DBMS_STATS Not seen yet

Hash Cluster Creation " " "

Hash Joins Phase 2 " " "  

   formatted blocks.  

In addition, an API has been produced to allow users to define their

own Longops and monitor them. This script demonstrates how to run a job

and maintain the longops info, and has a second script to run to

monitor progress. Cut the scripts at the line of stars and execute

in two seperate sessions.  

REM Create sequence for this demonstration first:

create sequence longops_demo_seq;

REM REM This script demonstrates a sample use of the interface to

REM v$session_longops to allow the application developer to

REM insert and manipulate their own rows in this memory array

REM The only prerequisites for this script are a big_emp table

REM to select from, and a sequence called longops_demo_seq.

REM The pl/sql block only opens a cursor and fetches from it

REM until %NOTFOUND. If anything else is needed, place your own

REM specific processing below 'User operation comes in here'

REM REM REM Andrew Holland ( )February 1999

REM DECLARE    l_ename VARCHAR2(30);

   l_rindex NUMBER; -- Index to the slot in v$session_longops

   l_sltno NUMBER; -- some sort of internal status field

   l_seq NUMBER; -- Unique ID for the 'Run'

   l_count NUMBER; -- of employees

   l_loopcnt NUMBER :=0; -- of passes through the loop

   l_err NUMBER; -- For error handling



   CURSOR c_emp IS SELECT ename FROM scott.big_emp;



   CURSOR c_emp_count IS SELECT COUNT(*) FROM scott.big_emp;




   SELECT longops_demo_seq.nextval INTO l_seq FROM dual; 


-- Get the count of rows in the cursor

OPEN c_emp_count; FETCH c_emp_count INTO l_count; -- get the count of rows CLOSE c_emp_count;
OPEN c_emp; FETCH c_emp into l_ename; -- prefetch first row

-- Initialize The row in v$session_longops



-- Initialize the row in v$session_longops for our operation

dbms_application_info.set_session_longops( rindex=>l_rindex, -- This is the row in the array slno=>l_sltno, -- This is internal ???? op_name=>'Batch Job: '||l_seq, -- This is the name of the operation target=>null, -- Could be the obid we are working on context=>null, -- Client defined context sofar=>l_loopcnt, -- The number of times round the loop totalwork=>l_count, -- The target number of times round target_desc=>'Employees', -- Name of what we are processing units=>'People'); -- Units to measure target_desc in WHILE c_emp%FOUND LOOP -- l_loopcnt:=l_loopcnt+1; -- We've done a row at the start -- because we prefetched. dbms_application_info.set_session_longops( rindex=>l_rindex, -- The same row we just created. slno=>l_sltno, sofar=>l_loopcnt, totalwork=>l_count ); FETCH c_emp INTO l_ename; -- Fetch the next row. -- -- You may want to.......... dbms_lock.sleep(1); -- Or something to slow the loop down a bit -- -- User operation comes in here if necessary. -- END LOOP; CLOSE c_emp; EXCEPTION
-- Don't really care it's a demo.
WHEN OTHERS THEN l_err := sqlcode; RAISE_APPLICATION_ERROR( -20001,'Error : '|| TO_CHAR(l_err)||' in Longops Demo'); END; / ************************************************************************* REM REM REM Is you want to see the above script in Action, then run the following REM SQL*Plus script many times while you job is running. REM You may want to get the serial# for 'Your' Job first REM REM COLUMN percent FORMAT 999.99 REM COLUMN message FORMAT A50 WORD REM SELECT to_char(start_time,'hh24:mi:ss') stime, REM message,( sofar/totalwork)* 100 percent REM FROM v$session_longops REM / -----Original Message----- From: Jacques Kilchoer [] Sent: Friday, September 10, 2004 5:27 PM To:; Subject: RE: V$SESSION_LONGOPS Are you sure that the operation you were monitoring would have activity that would cause it to show in v$session_longops? Metalink note 68438.1 describes the kind of things that would show up in that view in an Oracle 8.1 database. There might be an updated Metalink note somewhere that shows the behaviour in Oracle 9.2 ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- To unsubscribe - To search the archives -
Received on Fri Sep 10 2004 - 17:58:17 CDT

Original text of this message