Hi Tom,

There was a thread at comp.databases.oracle.server about the uniqueness of transaction ID. Used to be that transaction ID can run out and the associated rollback segment needs to be dropped and recreated. I wonder how the the ORACLE 9i auto
undo management handles this. Will ORACLE recycle transaction IDs? If not, what happens when the transaction IDs run out?

Dear Tom,

Please consider this:

SQL*Plus: Release 9.2.0.3.0 - Production on Thu Aug 28 17:35:07 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production


Case 1:
=======
SQL> SET TRANSACTION READ ONLY;

Transaction set.

SQL> select select XIDUSN, XIDSLOT,XIDSQN from v$transaction;

SQL> select XIDUSN, XIDSLOT,XIDSQN from v$transaction;

no rows selected

SQL> commit;

Commit complete.

SQL> select XIDUSN, XIDSLOT,XIDSQN from v$transaction;

no rows selected


Case 2:
=======
SQL> SET TRANSACTION READ ONLY;

Transaction set.

SQL> select XIDUSN, XIDSLOT,XIDSQN from v$transaction;

no rows selected

SQL> SELECT dbms_transaction.local_transaction_id FROM dual;

LOCAL_TRANSACTION_ID
------------------------------------------------------------------------------

7.19.23877

SQL> select XIDUSN, XIDSLOT,XIDSQN from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         7         19      23877

SQL>

=================

Is it a bug in dbms_transaction.local_transaction_id or expected behaviour and,  if the latter,  why in Case 1 there is no transaction record in v$transaction ?

Thank you.
 

it would be a bug if you were able to actually update something.


ops$tkyte@ORA920> SELECT dbms_transaction.local_transaction_id FROM dual;
 
LOCAL_TRANSACTION_ID
-----------------------------------------------------------------------------------------------------------------------------------
4.9.19278
 
ops$tkyte@ORA920> update emp set empno = empno;
update emp set empno = empno
       *
ERROR at line 1:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction


but you cannot. so, no bug... you are "read only"


In case #1, why would you "expect" a record in v$transaction?  you don't really "need" one 

OK, probably I was not clear enough.

You said:
"In case #1, why would you "expect" a record in v$transaction? you don't really "need" one "

I never expected any record in v$transaction in Case 1. Thank you for confirming that.

Now as to Case 2, the real question is:

Why does running 'SELECT dbms_transaction.local_transaction_id FROM dual;' creates an entry in v$transaction ? That's what my Case 2 demonstrates. Clearly there is no update/delete/insert.

dbms_transaction.local_transaction_id creating this entry is quite misleading. What's the point of having a transaction id for the read-only transaction (isolation level read-only) ?

Thank you.

OK.

The transaction information appearing as the result of calling the function is misleading for several reasons:

1. You said 'why would you "expect" a record in vtransaction? you don't really "need" one'. That's a fair statement for the read-only transaction -- after all it (the transaction) does not generate undo and, therefore, the transaction id (XIDUSN, XIDSLOT, XIDSQN) does not have any meaning in the context of the read-only transaction.

2. Taking the above into account, what's the real meaning of the transaction id created as a result of dbms_transaction.local_transaction_id execution ? Just a number without any meanining ? If so, what's the use of such a number ?

3. Does Oracle create a 'real' transaction entry in v$transaction where XIDUSN, XIDSLOT, XIDSQN have the usual semantics just in order to return a transaction id ? If so, it's quite counter-intuitive for someone accustomed to seeing entries in v$transaction only for update/insert/deletes.

Thanks.

You said 'the act of asking -- makes one appear.  why is it misleading -- you are *in* a transaction.'

Consider this:

SQL>  alter session set isolation_level=serializable;

Session altered.

SQL> select * from t;

         X
----------
         1

SQL> SELECT dbms_transaction.local_transaction_id FROM dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
16.81.7410

/* So far so good -- I am in the transaction */
SQL> commit;

Commit complete.

SQL> alter session set isolation_level=read committed;

Session altered.

SQL> select * from t;

         X
----------
         1

SQL> SELECT dbms_transaction.local_transaction_id FROM dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------

/* How come ? I am not in a transaction ? Or a transaction running in the READ COMMITTED mode is not quite a transaction */

SQL> commit;

Commit complete.

SQL>


Wouldn't you agree that the behaviour is quite inconsistent ?  Under the SERIALIZABLE/READ-ONLY level I can get a transaction id plus an entry in v$transaction as a bonus whilst in READ COMMITTED I get nothing ? As an aside,  I do understand how read consistency is achieved in both mode and one can probably rationalize why dbms_transaction.local_transaction_id behaves differently but it would not make the behaviour consistent surely.

Thanks. 

"
the rbs's in the undo tablespace are created, dropped, resized all of the time.
It is interesting to monitor your alert log -- ramp up concurrent users and
watch the database automatically create and online new rbs to handle the load --
and if you are patient enough, watch it offline and drop them later when they
are not needed anymore.
"

1. Is there a view to monitor rbs dropped and rbs added
2. Leaving aside "flashback queries" what is the best way
to monitor and resize undo tablespace, to optimal size

how can i put icon on button in oracale 9i and how can i save on client?

Hello Tom,

I have one requirement, in one single transaction I have to do some processing only once. To address this requirement I am planning to use dbms_transaction.local_transaction_id.

Means on update <BaseTable> trigger executes and does insertion in some other tables, but in some transaction if I update same table multiple times (irrelevant of old/new values) NO insertion should happen in other tables.

Could you please elaborate on this as it is not clear to me how to reset package variable at the end of the transaction.

Please help, i would appreciate if you provide an example.

Thanks & Regards,

Shailesh