On Fri, 16 Feb 2001, Eskov Anton wrote:
> Hi
> Is there any way to get notified when COMMIT/ROLLBACK statement is
> executed?
>
> SY
> Anton
Here's an example. I'll leave making use of this as an
exercise for the reader. ;)
Jared
------------
declare
cursor c_trans_count ( statname_in varchar2 )
is
select value
from v$sysstat
where name = statname_in;
v_commit_count_old pls_integer;
v_commit_count_new pls_integer;
v_rollback_count_old pls_integer;
v_rollback_count_new pls_integer;
v_commit_name varchar2(30) := 'user commits';
v_rollback_name varchar2(30) := 'user rollbacks';
begin
open c_trans_count(v_commit_name);
fetch c_trans_count into v_commit_count_new;
close c_trans_count;
v_commit_count_old := v_commit_count_new;
open c_trans_count(v_rollback_name);
fetch c_trans_count into v_rollback_count_new;
close c_trans_count;
v_rollback_count_old := v_rollback_count_new;
for x in 1..10
loop
open c_trans_count(v_commit_name);
fetch c_trans_count into v_commit_count_new;
close c_trans_count;
open c_trans_count(v_rollback_name);
fetch c_trans_count into v_rollback_count_new;
close c_trans_count;
if v_commit_count_old != v_commit_count_new
then
dbms_output.put_line('user commit at ' ||
to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
end if;
if v_rollback_count_old != v_rollback_count_new
then
dbms_output.put_line('user rollback at ' ||
to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
end if;
v_commit_count_old := v_commit_count_new;
v_rollback_count_old := v_rollback_count_new;
dbms_lock.sleep(1);
end loop;
end;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
