Re: Is transaction ID unique for the life of a DB?

I did notice from the output, Yong, that the combination of 3 numbers are indeed unique. My conclusion was based on the thought that ORACLE has no reasons of keeping track of the transaction ID (not SCN) of either committed or rollbacked transactions. Therefore one can not base application design on the assumption that they will be unique for the "lifetime" of a database.

Dropping and re-creating rollback segments may indeed cause a few transaction IDs to be recycled. This can certainly happen during the "lifetime" of a database. Actually, it has less to do with "lifetime" of a database and more to do with number of transactions the database executes. My guess is that the # of unique transaction ID ORACLE can create is

(# of rollback segments)*(# of slots in the RBS TX table)*(max sequence value)

Form what I can tell, there are 20 slots in the RBS TX table. Assuming you have only one rollback segment, the max # of unique transactions IDs will be

20*(max sequence value)

Forget about "lifetime". If the database executes 20*(max sequence value) of transactions a day or an hour, I would bet transaction IDs will recycle. And I think ORACLE uses the "CYCLE" clause for the sequence so no error will be generated when the sequence hits the max and recycle.

• Jusung Yang

yong321_at_yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.0302140939.433d33e3_at_posting.google.com>...
> Hi, Jusung,
>
> I looked at all the numbers in your output. It doesn't look like
> there're duplicates when rollback segment# and slot# are taken into
> account. I have a feeling that transaction IDs may well be unique for
> the lifetime of the database, unless a rollback segment is dropped and
> a new one is created with that segment number.
>
> After I offlined all except one non-system rollback segments to
> simplify the test, I ran your program many times. I also tested with
> some iterations calling commit instead of rollback on your line 8. The
> cumulative result never showed a duplicate seg#.slot#.seq#
> combination.
>
> Yong Huang
>
> JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0302121209.66b98be4_at_posting.google.com>...
> > I think you meant DBMS_TRANSACTION, not DBA_TRANSACTION. Due to
> > dynamic nature of the v$ views, I doubt transaction ID will be unique
> > for the "life" of a database. There is no reasons for ORACLE to keep
> > track of "committed" transaction ID. My test showed ORACLE indeed
> > recycles the sequence number, xidsqn. So for sure xidsqn will not be
> > unique for the life time of a database. I would venture to say
> > USN.SLOT.SQN will not be unique. For active transactions, yes. But not
> > for committed transactions. Here is a simple test. As can be seen,
> > there are many repeated sqn.
> >
> > SQL> declare
> > 2 x varchar2(100);
> > 3 begin
> > 4 for i in 1..100 loop
> > 5 update test1 set sumc2=0;
> > 6 x:=dbms_transaction.local_transaction_id;
> > 7 dbms_output.put_line(x);
> > 8 rollback;
> > 9 end loop;
> > 10 end;
> > 11 /
> > 8.41.57484
> > 9.37.57361
> ...
Received on Fri Feb 14 2003 - 18:08:14 CST