This content has been marked as final. Show 12 replies
If you are interested to find the active transactions using undo extents then you can get the amount of undo
SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;
dba_rollback_segs and v$rollstat views are for more information. V$TRANSACTION.USED_UBLK will tell you Number of undo blocks used.
There are great scripts at Sir Steve Adam's site : http://www.ixora.com.au/scripts/rbs.htm
Useful information indeed, thanks.
I was specifically more interested in finding a way to get statistics related to the amount of times a "rollback" statement is issued by a user or applications. Reason being we wanted to monitor some inconsistencies related to trigger execution.
some inconsistencies related to trigger execution.What exactly it mean? Which type of inconsistencies are you talking about/addressing? And you are going to something audit the commit / rollback? Oracle don't provides audit on commit and rollback. Just post the code of trigger; so that if possible, we may try to help you.
Did not want to raise another issue :)
We have a few huge tables which we need to monitor in terms of activities so that we're using triggers to update some oracle sequences keeping track of the action performed on them.
basically the trigger only increase the sequence value.
CREATE OR REPLACE TRIGGER xxx_Summary_Trigger
AFTER INSERT OR UPDATE or delete ON xxx
FOR EACH ROW
declare dummy number(10);
IF :New.xxx_Status = 'used' THEN
SELECT used_plus.nextval into dummy from dual;
ELSIF :New.Card_Status = 'activated' THEN
SELECT activated_plus.nextval into dummy from dual;
IF :Old.xxx_Status = 'used' THEN
SELECT used_minus.nextval into dummy from dual;
ELSIF :Old.xxx_Status = 'activated' THEN
SELECT activated_minus.nextval into dummy from dual;
So that each time the xxx_Status is updated we have an updated sequence (nocache) which allows us to get the overall distribution without a long scan on the table (hosting more than 200M records).
I know triggers are not the best way to approach, not to mention they fire before a commit, so that in case of a rollback the resulting sequence would be showing a wrong value.
But this was only a quick and dirt approach, which works fine for the time being. I just wanted to confirm the inconsistencies we are encountering are due to some rollbacks which mislead the trigger, that's why I was interested in getting a stat on the rollback statement execution.
We get discrepancies every now and then, so that leaving traces on for a long time may be disruptive.
Aha... so, you are trying to rollback the sequence by rollback segment? Mike, there is a big flaw in your design. You are using sequence (an object which only used to generate unique numbers) in trigger and wish that sequence should be rollback on rollbacking the transaction, which is not possible, because commit and and rollback are used for transaction, rolling back of sequence is no more concern/connected. If you wish to rollback the sequence (which is really a horrible idea though), you would have to increment with -1. Either you will have to use another logic (something by using Select .... For update Nowait) or try to read this (Re: Question on Sequence.. thread. There is no rollback trigger command in Oracle like Sybase.
I see what you mean, but we were not trying to rollback the sequence value. We know the limit of the trigger in not being able to fire after the commit, but we wanted to see if the issues we're having are related to rollback statements issued by the app, or by users connected directly to DB for instance.
Thanks for your time, will check anyway for alternatives among the info you shared.
Maybe you have some triggers restarts. This is briefly document in http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#i1006211:
If the triggering statement of a BEFORE statement trigger is an UPDATE or DELETE statement that conflicts with an UPDATE statement that is running, then the database does a transparent ROLLBACK to SAVEPOINT and restarts the triggering statement. The database can do this many times before the triggering statement completes successfully. Each time the database restarts the triggering statement, the trigger fires. The ROLLBACK to SAVEPOINT does not undo changes to package variables that the trigger references. To detect this situation, include a counter variable in the package.
You can read more on this in :
It is possible that this issue occurs with Oracle 9i and AFTER TRIGGER.
Really Interesting documentation...
Actually we are using the trigger only to increment the sequence, which as you may have seen we're using basically as a counter.
Not sure if the parallel access to sequence objects causes locks which may cause the restart,but it's worth investigating this side as well.
Will check, appreciated!