Extra rows returned on V$transaction query.

From: Rob G <ontarioloverr_at_gmail.com>
Date: Sun, 26 Nov 2006 17:51:39 -0800
Message-ID: <6bb8bdaf0611261751o7a009adehf19f1f40a8104160@mail.gmail.com>

Hi all,
I was running a transaction and I was looking into v$transaction on the usage of undo blocks. While monitoring sometimes it returned 2 or 3 rows, but mostly one row. What do the second and third row represent in this case:

12:33:35 SQL> select XIDUSN, START_TIME, SES_ADDR, NAME, USED_UBLK, STATUS
12:34:18   2  from v$transaction
12:34:31   3  where SES_ADDR = 'BA7664FC';

    XIDUSN START_TIME           SES_ADDR


---------- -------------------- --------

NAME

---

 USED_UBLK STATUS
---------- ----------------

         8 11/26/06 11:49:25 BA7664FC

    127101 ACTIVE 12:34:52 SQL> /     XIDUSN START_TIME SES_ADDR
---------- -------------------- --------
NAME

---

 USED_UBLK STATUS
---------- ----------------

         8 11/26/06 11:49:25 BA7664FC

    127897 ACTIVE 12:35:10 SQL> / /************* THREE ROWS RETURNED HERE

***********************************************/

    XIDUSN START_TIME           SES_ADDR


---------- -------------------- --------

NAME

---

 USED_UBLK STATUS
---------- ----------------

         8 11/26/06 12:35:13 BA7664FC

         0 INACTIVE          8 11/26/06 11:49:25 BA7664FC

    128023 ACTIVE          8 11/26/06 12:35:13 BA7664FC

         2 ACTIVE 12:35:13 SQL> l
  1 select XIDUSN, START_TIME, SES_ADDR, NAME, USED_UBLK, STATUS   2 from v$transaction
  3* where SES_ADDR = 'BA7664FC'
12:35:20 SQL> /     XIDUSN START_TIME SES_ADDR
---------- -------------------- --------
NAME

---

 USED_UBLK STATUS
---------- ----------------

         8 11/26/06 11:49:25 BA7664FC

    128373 ACTIVE 12:35:21 SQL> 12:36:11 SQL> / /************* TWO ROWS RETURNED HERE

***********************************************/

    XIDUSN START_TIME           SES_ADDR


---------- -------------------- --------

NAME

---

 USED_UBLK STATUS
---------- ----------------

         8 11/26/06 11:49:25 BA7664FC

    130772 ACTIVE          8 11/26/06 12:36:11 BA7664FC

         3 ACTIVE 12:36:11 SQL>
Oracle is 9.2

--

Cheers,
Rob :)

--

http://www.freelists.org/webpage/oracle-l Received on Sun Nov 26 2006 - 19:51:39 CST