Skip to Main Content
  • Questions

  • how to fetch sqlid from old transaction_id

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sourav.

Asked: December 09, 2016 - 3:06 am UTC

Last updated: March 08, 2018 - 3:52 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi,

We have Goldengate 11gR2 for Unidirectional replication from Oracle(11.2.0.4) to Oracle(11.2.0.4) database. Both source and target are RAC environment.

We received below warning message on Goldengate, letting us know why one of the extract was lagging behind.

2016-12-05 04:17:43 WARNING OGG-01027 Long Running Transaction: XID 43.12.8362282, Items 0, Extract EINTA, Redo Thread 3, SCN 2947.3064867020 (12574428091412), Redo Seq #87398, Redo RBA 72457272.

By the time, I checked transaction(XID 43.12.8362282) on gv$transaction view, it was completed and wasn't available any more. I checked gv$session and the session was over.

I job is to find out the SQL that was delaying the GG Extract. So I would like to establish some way to fetch the SQL_ID from gv$active_session_history or dba_hist_active_sess_history view using XID 43.12.8362282. But I'm not able to find any view which has any further detail on XID 43.12.8362282.

Please help


and Connor said...

Try the flashback views perhaps, eg

SQL> SELECT xid, UNDO_SQL
  2  FROM   flashback_transaction_query 
  2  WHERE  xid = hextoraw('09000D001D050000');

XID              UNDO_SQL
---------------- ------------------------------------
09000D001D050000 update "SCOTT"."DEPT" 
                 set "DNAME" = 'BEAN COUNTERS' 
                 where ROWID = 'AAAQ+hAAEAAAAAOAAA';


Rating

  (4 ratings)

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

Comments

how to get hex value of XID

Sourav Biswas, December 12, 2016 - 3:37 am UTC

Hi Connor,

In my scenario, all I have is XID=43.12.8362282. How do I covent them to Hexadecimal number to use flashback_transaction_query view.

Please suggest.

- Sourav
Connor McDonald
December 13, 2016 - 1:35 am UTC

It is simply the hex concatenation of them. 

SQL> insert into t values (1);

1 row created.

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

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
        19         28       1542

1 row selected.

SQL> commit;

Commit complete.

SQL> select versions_xid from t versions between scn minvalue and maxvalue;

VERSIONS_XID
----------------
1300 1C00 06060000   -- spaces here added by me

1 row selected.

SQL> select to_char(19,'xxxx'), to_char(28,'xxxx'), to_char(1542,'xxxxxx') from dual;

TO_CH TO_CH TO_CHAR
----- ----- -------
   13    1c     606

1 row selected.

Sourav Biswas, December 14, 2016 - 10:34 pm UTC

Hi Connor,

Please find below, my test case:-

SCOTT@aqua> insert into t values (1);

1 row created.

SYS@aqua> select xidusn, xidslot,xidsqn from v$transaction;

XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
9 15 904

SCOTT@aqua> commit;

Commit complete.

SCOTT@aqua> select * from t;

SR_NO
----------
1

1 row selected.

SCOTT@aqua> select versions_xid from t versions between scn minvalue and maxvalue;

VERSIONS_XID
----------------
09000F0088030000

1 row selected.

SCOTT@aqua> select to_char(9,'xxxx'), to_char(15, 'xxxx'), to_char(904,'xxxxxx') from dual;

TO_CH TO_CH TO_CHAR
----- ----- -------
9 f 388

1 row selected.


I would like to know, why "to_char(904,'xxxxxx')" is showing different from Version_XID column.

Also, when I use above hex number to gather the SQL, I get below output:-

SYS@aqua> SELECT xid, UNDO_SQL FROM flashback_transaction_query WHERE xid = hextoraw('09000F0088030000');

XID UNDO_SQL
---------------- ----------------------------------------------------------------------
09000F0088030000 delete from "SCOTT"."T" where ROWID = 'AAAXQdAAHAAAAX3AAA';


This is showing be the UNDO SQL, I was looking at the REDO SQL, i.e.,

insert into t values (1)

As I'm trying to fetch the query which was long running in the database, I need the REDO sql to figure out. As GoldenGate Extract process is waiting for these long running queries. And I'm suppose to fetch those queries, and share with DEV team as well as the management.
Connor McDonald
December 15, 2016 - 2:34 am UTC

There is also the XID column on v$transaction. Have you tried that ?

to_char( XIDSQN ,'fmXXXXXXX') mismatch with v$transaction

Rajeshwaran Jeyabal, March 06, 2018 - 6:49 am UTC

Team,

Could you help us on this ?

The to_char( XIDSQN ,'fmXXXXXXX') doesn't match with XID in V$Transaction. please help.

demo@ORA12C> exec dbms_output.put_line( dbms_transaction.local_transaction_id(
10.15.11312

PL/SQL procedure successfully completed.

demo@ORA12C> select xidusn, xidslot, xidsqn, xid
  2  from v$transaction ;

    XIDUSN    XIDSLOT     XIDSQN XID
---------- ---------- ---------- ----------------
        10         15      11312 0A000F00302C0000

demo@ORA12C> select xidusn, xidslot, xidsqn, xid ,
  2    to_char( xidusn, 'fmXXXXX') x1,
  3    to_char( xidslot, 'fmXXXXX') x2,
  4    to_char( xidsqn, 'fmXXXXX') x3
  5  from v$transaction ;

    XIDUSN    XIDSLOT     XIDSQN XID              X1     X2     X3
---------- ---------- ---------- ---------------- ------ ------ ------
        10         15      11312 0A000F00302C0000 A      F      2C30

Connor McDonald
March 07, 2018 - 1:23 am UTC 

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

PL/SQL procedure successfully completed.

SQL> select xidusn, xidslot, xidsqn, xid from v$transaction ;

    XIDUSN    XIDSLOT     XIDSQN XID
---------- ---------- ---------- ----------------
         3          4      50081 03000400A1C30000

SQL> select xidusn, xidslot, xidsqn, xid ,
  2        to_char( xidusn, 'fmXXXXX') x1,
  3        to_char( xidslot, 'fmXXXXX') x2,
  4        to_char( xidsqn, 'fmXXXXX') x3
  5      from v$transaction ;

    XIDUSN    XIDSLOT     XIDSQN XID              X1     X2     X3
---------- ---------- ---------- ---------------- ------ ------ ------
         3          4      50081 03000400A1C30000 3      4      C3A1

SQL>
SQL>     select to_number('C3A1','XXXX') from dual;

TO_NUMBER('C3A1','XXXX')
------------------------
                   50081


It all matches up when you remember about endian format and byte swapping.

Hence "A1C30000" can be read as 00,00,C3,A1

to_char( XIDSQN ,'fmXXXXXXX') mismatch with v$transaction

Rajeshwaran Jeyabal, March 07, 2018 - 8:10 am UTC

....
It all matches up when you remember about endian format and byte swapping.
Hence "A1C30000" can be read as 00,00,C3,A1
....

can you please elaborate the above?

given the XIDUSN, XIDSLOT and XIDSQN - how can one assemble this back to XID correctly - considering the above endian format and byte swapping.
Connor McDonald
March 08, 2018 - 3:52 am UTC

A1C30000

is 4 bytes

A1, C3, 00, 00

reverse the bytes and you get

00, 00, C3, A1
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.