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

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 - 11:39:56 CST