Skip to Main Content
  • Questions
  • How to get unique transaction id of the current transaction?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Manish.

Asked: December 24, 2004 - 11:41 am UTC

Last updated: February 02, 2011 - 7:59 am UTC

Version: 10.1.0

Viewed 10K+ times! This question is

You Asked

We have a unique auditing requirement where we want to know all updates to the system by a 5 digit terminal no for a given time period. Each user input coming through the legacy network always has an associated 5 digit terminal no. This user input will be processed by WebLogic application server which may do lots of SQL updates to 10g RAC database. We know that these SQL updates will be logged by Oracle in Redo and Archive logs, which can be mined using Log Miner by giving transaction id (XIDUSN + XIDSLT + XIDSQN). So we were thinking of creating a table that can relate 5 digit terminal no to a series of oracle transaction id like the following:

TerminalNo timestamp XIDUSN XIDSLT XIDSQN
---------- --------- ------ ------ ------

However, in order to achive the above, We will need the transaction id of the current transaction (XIDUSN + XIDSLT + XIDSQN) before we commit all updates. Each transaction in the system will insert a row in this table just before commiting, which will enable us to get all updates by a terminal no for a given period.
So, how can I get the current transaction id of the transaction? Do you have any other idea for implementing same functionality.
Thanks


and Tom said...

dbms_transaction.LOCAL_TRANSACTION_ID

if you are in a transaction, that'll return it. else it'll return NULL by default. If you want, you can pass it a boolean of TRUE and if you are not in a transaction yet -- it'll start one.


ops$tkyte@ORA9IUTF> update emp set ename = ename;

14 rows updated.

ops$tkyte@ORA9IUTF> exec dbms_output.put_line( dbms_transaction.local_transaction_id );
1.16.158

PL/SQL procedure successfully completed.


Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Will the transaction id be unique even in a RAC environent

Manish Limaye, December 24, 2004 - 3:39 pm UTC

Thank you very much Tom. I attended Oracle OpenWorld earlier this month and your session was one of the best.

I have a followup query. It is my understanding that in a RAC environment, each instance has its own redo logs and archived logs. Will the transaction id obtained from DBMS_TRANSACTION.LOCAL_TRANSACTION_ID be unique to the instance or database? Our RAC environment is not ready yet and hence I can not test this. Here is what the manual says:"This function returns the local (to instance) unique identifier for the current transaction. It returns null if there is no current transaction".
If the id is unique only to the instance, Log miner may find 2 different transactions with same id. If this is true, we will have to store the instance id also.
Please comment.
Thanks.



Tom Kyte
December 24, 2004 - 4:01 pm UTC

XIDUSN Undo segment number
XIDSLOT Slot number
XIDSQN Sequence number

The XIDUSN, XIDSLOT, and XIDSQN identify the rollback segment, rollback segment slot, and rollback segment sequence (or wrap).

they are not "redo based". the RBS's are totally shared between the two instances. I'll file a doc bug.

However, I'd collect as much as I could or possibly thought I might want at some point, instance, the systimestamp for example would be two obvious ones.

RTM

Duke Ganote, December 24, 2004 - 3:46 pm UTC

Cool, even though I've been using DBMS_TRANSACTION.LOCAL_TRANSACTION_ID for several months, I'd overlooked that it has the optional create_transaction parameter, so you can create a transID without doing any some pseudo-DML:

SQL> set serveroutput on;
SQL> exec dbms_output.put_line(dbms_transaction.local_transaction_id);

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(dbms_transaction.local_transaction_id(TRUE));
7.12.15

PL/SQL procedure successfully completed.

In production, I capture the transID from within a trigger and pass it and the old/new info to a package.  Being able to generate the transID directly will help simplify some test case setups.  Thanks! 

unique transaction id really unique?

A reader, March 11, 2005 - 5:54 pm UTC

Hhm, interesting stuff ...

Just to clear it up for me:
1) Is the transaction id unique over time (never repeats)?
The "XIDSQN (Sequence number)" part seems to suggest that.

2) What would be the maximum length required for a
varchar(2) field to store that id?

Thanks Tom,
you are really the best!

Kind regards,
Steve


Tom Kyte
March 12, 2005 - 10:05 am UTC

1) the transaction id should be unique, the sequence is part of the undo segment/slot. I suppose over a really really really long time -- it could repeat (depends on whether they wrap it at 2/4 billion or not)

2) 48*3+2 for nnnnnnnnn.nnnnnnnnn.nnnnnnnnnnn would be "safe"

Why 48?

A reader, March 13, 2005 - 6:32 am UTC

Thanks a lot, Tom. That cleared it up for me.

But why 48 * 3? "Desc v$transaction" shows that
XIDUSN, XIDSLT, XIDSQN are numbers, so I would
have expected 38 * 3. Anything wrong with my reasoning
here?

Kind regards,
Steve



Tom Kyte
March 13, 2005 - 9:57 am UTC

the biggest format for a number is something like 44 characters (sign, decimal, whatever).

48 is safe.

How about DDL-statement?

Petri Rautakoski, April 26, 2005 - 2:56 am UTC

I just found out that DDL-statement does implicit commit and transaction id is changed that way. So how can I get unique transaction id that will be unchanged in DDL-statements? I also tested this:

CREATE TABLE test1 (test INTEGER);
INSERT INTO test1 VALUES (1);
CREATE TABLE test2 (test INTEGER);
ROLLBACK;

SELECT * FROM test1;

+------+
| TEST |
+------+
| 1 |
+------+
1 row in set (0.003 secs)

So rollback won't work if DDL-statement executed during transaction. Is this what's needed?

Tom Kyte
April 26, 2005 - 7:26 am UTC

ddl is processed like this:


begin
COMMIT;
begin
do the ddl;
COMMIT;
exception
when others then rollback;
RAISE
end;
end;
/

a commit always happens first, THEN the ddl, then a commit or rollback of the DDL.

What are you trying to do? What is the unique transaction ID for? (there are a couple of transactions in there with DDL).




How about DDL-statement? (continue)

A reader, April 26, 2005 - 7:50 am UTC

I'm tracking things (like tables created) with LOCAL_TRANSACTION_ID so that when this transaction id doesn't exist in sys.v$_transactions table I can be sure that owner of table is not valid anymore. But this DDL-part seems to make my design worthless.

Tom Kyte
April 26, 2005 - 8:08 am UTC

Can you elaborate more on what your goal is, I did not understand what you meant by this:

...I'm tracking things (like tables created) with LOCAL_TRANSACTION_ID so that when this transaction id doesn't exist in sys.v$_transactions table I can be sure
that owner of table is not valid anymore.....


with DDL, the transaction id comes and goes so far, it would be rare to even see it. Not sure what you mean by "that owner of the table is not valid anymore..."

What are you doing?

How about DDL-statement? (continue)

A reader, April 26, 2005 - 8:18 am UTC

I add creator of the table into log-table. Creator is here LOCAL_TRANSACTION_ID and named as "owner of table".

In my application tables are created and dropped all the time and I have to somehow track when owner has "died" and not dropped tables in normal way. So I select all valid transactions from sys.v$_transactions-table ang if I find not valid transactions from log-table I can drop tables related to that transaction id.

Tom Kyte
April 26, 2005 - 8:32 am UTC

I'm very worried about your application then.

Doing DDL like that is way up in the top five of "things NOT to do". Way up there.

Why are you compelled to create tables like that? Creating and dropping tables frequently is a *really* bad idea, what can we do to help you change this approach before you get too far into it.

How about DDL-statement? (continue)

A reader, April 26, 2005 - 8:49 am UTC

Thanks for your advice. ;o)

We have so large amount of data that it's impossible to process in memory. So, the logical place to do processing is database. These tables are totally temporary and dropped after process has ended. This data is also dynamic so it can't be handled in global temporary table.

Tom Kyte
April 26, 2005 - 9:01 am UTC

sure it can be (handled in a gtt) if needed. give yourself a gtt with 50 varchar2 4000's, 50 numbers and 50 dates. there you go (for example).

But Oracle handles lots of data for you -- you are going to have to convince me that you need these gtt's even -- I've yet to see it actually happen.

I can assure you, going down the path of tons of create tables and tons of drop tables is going to be *a really bad idea*.

I have ideas about how you could do what you ask (clean up tables left by sessions no longer in the database) via naming conventions, but I hesitate to even bring it up because it is a recipe for disaster here.

How about DDL-statement? (continue)

A reader, April 26, 2005 - 10:20 am UTC

I know it's a bad idea, but we couldn't solve it otherwise. We cannot use a gtt because sometimes we may need 1000 of varchar columns and sometimes 100 varchars and 100 numbers (for example). The number of rows is usually less than 10 millions, but can be up to 100 millions / file.

We tried to store columns as rows in another table, but the performance was not very good and implementing functions on that format was too difficult.

What bad can we expect when creating and dropping lots of temporary tables?
Thanks.

Tom Kyte
April 26, 2005 - 10:32 am UTC

doing dll is a serial thing.
you'll bash the heck out of the dictionary.
you won't be able to use stored procedures for anything, unless you use dynamic sql.
you'll hard parse like mad as not only is the sql unique, the objects are.


I cannot think of a good thing to say.

use 1000 varchar2's then if you must.

Describe your processing that requires such an implementation for us and why it cannot be done in an existing set of tables.

how serial is the serial

Michael Norbert, April 26, 2005 - 2:37 pm UTC

How serial is ddl? Until all temporary extents are allocated, converted to permanent? Does LMT make a difference?

Tom Kyte
April 26, 2005 - 2:50 pm UTC

it won't be for the entire duration of the statement (eg: the population of the table)

doing ddl in a live system alot is a recipe for disaster. it is not something I would ever recommend

How about DDL-statement? (continue)

A reader, April 28, 2005 - 6:05 am UTC

The role of our database is not very conventional, its not for storing but for processing data.
It has two funtions:
1. its serves as a virtual memory for some of our 'tools'. Instead of keeping the file in memory it is dumped into the database for processing -> no more out of memory errors.
2. it provides simple operations like sorting, grouping, removing duplicates, etc.

We have just few concurrent users (tools), transactions may take several minutes to complete. So, serial ddl is not a problem for us.

Not able to use stored procedures... well, we have implemented the tools by generating SQL in java and it works for us quite well.

Doing lots of hard parsing... If most of SQL executes long time, doing hard parse every time doesn't seem to be a big deal to me.

I really cannot explain here all the processing we do. But, we have a file format and code that maps that to the simplest possible database structure. This way we have been able to implement some really difficult tools. Doing the same with set of existing table means different table structure... much more difficult.

Could you please explain what you meant with "you'll bash the heck out of the dictionary"?

Thanks


Tom Kyte
April 28, 2005 - 8:24 am UTC

don't know how to put "bash the heck out of the data dictionary" differently.  you will be doing tons of drops and creates.

(oh the damage to the shared pool too -- you should expect unconventional issues to hit you over time as you are doing very unconventional things here).  

You might find if you actually designed your DATA oriented application to *use* the database -- your queries wouldn't run for long times.  But anyway.


So, use a naming convention -- have each session append _<NNN> to the end of their object names, where _NNN is their AUDSID (auditing sid)


ops$tkyte@ORA9IR2> select audsid from v$session where sid = ( select sid from v$mystat where rownum=1);
 
    AUDSID
----------
      4938


cleanup process looks for objects named with audsids that don't exist anymore. 

RAC and application transaction ids derived from sequences?

A reader, June 27, 2005 - 2:47 pm UTC

Tom,

We run Oracle applications on a 4 node RAC environment. One of the modules uses a db sequence with cache size of 20, to generate its unique ids. There is another module (both vanilla Oracle) that relies on the first app's record ids to be in serial order (by creation date of records). There are obviously times when that order gets whacked! The second app then crashes because it sees the end before the start!

So we're now discussing possible bug fixes with support/dev. Would the txn id from dbms_transaction, combined with systimestamp ensure this serial order?

Thanks!

Tom Kyte
June 27, 2005 - 5:16 pm UTC

that application is broken on a single instance of Oracle.

There is no assurance that the sequences will be in order by creation date of records on a single instance.


What are they trying to order? based on transaction commit time? the timestamp associated with the record? or what?

It seems that only their BUSINESS logic can answer this question.

order ?

lh, June 28, 2005 - 3:34 am UTC

Has this sequence been created with order - attribute ?
Order attribute is very useful in rac environments.

Tom Kyte
June 28, 2005 - 7:51 am UTC

order is a performace killer in RAC. This application has a bug that exists in single instance as well as RAC.

transaction id

sam, January 31, 2011 - 5:37 pm UTC

Tom:

If I want to capture audit data for DML and I have a trigger per table.

If one transaction update data in 4 tables and I want to capture the transacton id so I can know what the data updates for this transaction, Would you use the statement above to get the transaction id value in the trigger and store it in all of the 4 audit tables (one SHADOW audit table per production table)?

2) This does not sound a numeric number. What column size and type you would use for it?

3) Can i use a sequence number instead?
Tom Kyte
February 01, 2011 - 4:47 pm UTC

2) it is obviously not a numeric number:

ops$tkyte@ORA9IUTF> exec dbms_output.put_line( 
dbms_transaction.local_transaction_id );
1.16.158



varchar2(30) should handle it.

3) you tell me - you are a programmer, if we can give you the local transaction id, could you use a sequence number to somehow relate to it?

(yes, you could, but it would require a lot of work on your part - your code would have to detect when the transaction id changed in a given session so you could know to use nextval instead of currval)

trnasaction id

sam, February 01, 2011 - 4:57 pm UTC

Tom:

I see what you are saying.

So you would capture this oracle generated string (TRANS_ID) in all the TABLE TRIGGERS to store the transaction id in all T_AUDIT tables?

Kind of strange they dont use a unique number for that.
Tom Kyte
February 01, 2011 - 6:31 pm UTC

... Kind of strange they dont use a unique number for that.
...

not really, that is the real transaction id - it contains information that can be used to see the undo segment associated with the transaction - which is important to US. It is our transaction id, it is really three bits of data and it is what we needed to identify where a transaction was.



The seq(wrap)?

jiang huang zheng, February 02, 2011 - 4:00 am UTC

Hi Tom,

you say:
The XIDUSN, XIDSLOT, and XIDSQN identify the rollback segment, rollback segment slot, and rollback
segment sequence (or wrap).
the rollback segment sequence(or wrap), if the sequence is 158, does it mean that the rollback segment just wraps 158 times? what does it mean?

Thanks,

Tom Kyte
February 02, 2011 - 7:59 am UTC

Just consider it a number used to make the entire key unique. The three components together form a unique key - since xidusn and xidslot are not unique - they needed a bit more to do so. Enter the xidsqn, every time a slot is reused, the sequence goes up.

Thanks and I got it!

jiang huang zheng, February 02, 2011 - 10:17 pm UTC


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library