Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Notify when commiting
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