Categories
Blogroll dba plsql sql

v$session_longops in 10gR2

I have read an excellent overview of DBMS_APPLICATION_INFO in Andy Campbell blog on If only…

I have written a procedure P which is executing 5 statements. I want to add the name of the procedure P as a target. I do not want to hardcode the name however… So I can use $$PLSQL_UNIT in 10gR2 (documented in Using Predefined Inquiry Directives With Conditional Compilation)

Ok, here I go

create or replace procedure p is
rindex binary_integer;
slno binary_integer;
sofar number;
totalwork number;
target number;
begin
totalwork := 5;
sofar := 0;
select object_id
into target
from user_objects
where object_name = $$PLSQL_UNIT;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
end;
/

Now I execute it

SQL> exec p

PL/SQL procedure successfully completed.

and monitor it

SQL> select sid,opname,target,sofar,totalwork,units
2 from v$session_longops;
SID OPNAME TARGET SOFAR TOTALWORK UNITS
---- ------------ -------- ----- --------- ---------
538 Executing... SCOTT.P 5 5 Statement

By Laurent Schneider

Oracle Certified Master

3 replies on “v$session_longops in 10gR2”

I noticed that op_name, target, target_description and units cannot be changed, so now I do, before starting

DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex=>rindex,
slno=>slno,
op_name=>’execute…’,
target=>target,
units=>’statements’);

and later

DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex=>rindex,
slno=>slno,
sofar=>sofar,
totalwork=>totalwork)

a little bit less to type 🙂

Can this be used with Perl scripting? I’ve used set_session_info in perl but I’m wondering how to use set_session_longops in a code loop other than PL/SQL.

Leave a Reply

Your email address will not be published.