Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Notify when commiting

Re: Notify when commiting

From: <esiyuri_at_my-dejanews.com>
Date: Thu, 28 Jan 1999 10:49:10 GMT
Message-ID: <78pfb5$td1$1@nnrp1.dejanews.com>


In article <36AD8472.7D42F88_at_isg.de>,
  Markus Kopp <kopp_at_isg.de> wrote:
> Hi,
>
> I wanna write a program that will be notifyed, each time a commit
> occurs, how can I do that (something like a trigger or so)

Markus,

I really can't think why you would need (or want!) to know everytime a commit occurs. Maybe if you could explain your reasons you might get a better answer.

Anyway, I'll try to help.

(1) If you want to know when a particular transaction commits, and you have the ability to issue a command *within* that transaction, then you could use the dbms_alert package - see the "Oracle7 Server Application Developer's Guide" for details. You could signal an alert in the session(s) you want to trace, and when they commit, your "monitor" session would receive the alert. Obviously this will only work if you have the ability to add the dbms_alert.signal call to whatever program you wish to monitor.

(2) An alternative is to get the number of commits from the V$SESSTAT table. Try the following:

SQL> select s.sid, u.username, n.name, s.value

     from v$sesstat s, v$statname n, v$session u
     where s.statistic# = n.statistic#
     and u.username is not null
     and s.sid = u.sid
     and n.name = 'user commits'
     order by s.sid;

 SID USERNAME   NAME              VALUE
---- ---------- -------------- --------
   6 SYSTEM     user commits          0
  10 SCOTT      user commits          2

You could issue this query every now-and-then to keep track of how many commits have been issued. Implicit commits (from DDL) are counted, commits when there are no pending changes aren't.

(3) If you have an idea of what sort of actions you want to monitor, you could create insert/update/delete triggers on whichever table(s) is/are being changed. This is probably more useful than just counting the number of commits!

I hope this helps. If not, please explain what it is you are trying to do.

Regards
Yuri McPhedran

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Jan 28 1999 - 04:49:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US