flashback_transaction

From: Paul Baumgartel <paul.baumgartel_at_gmail.com>
Date: Mon, 9 May 2005 17:34:54 -0400
Message-ID: <f8c4771050509143410141834@mail.gmail.com>

I'm experimenting with flashback and I am puzzled with the results of querying flashback_transaction_query. Specifically, I have a transaction that updates 18 rows in a table. After the update, a flashback query against the table returns (via the versions_xid pseudocolumn) the transaction ID that performed the update. Then, a query of flashback_transaction_query on that xid returns 1 row, not 18 as I would have expected. The undo_sql is accurate for restoring one row of the 18 updated, but what about the other 17?

PB_at_t03a> select current_scn from v$database;

CURRENT_SCN

353809838

PB_at_t03a> update foo set timestamp=3Dsysdate,object_id=3Dmod(object_id,5) where owner =3D'SYSTEM';

18 rows updated.

PB_at_t03a> commit;

Commit complete.

PB_at_t03a> select timestamp, object_id, versions_xid from foo 2 versions between scn 353809838 and maxvalue where owner =3D 'SYSTEM 3 order by versions_xid';

TIMESTAMP OBJECT_ID VERSIONS_XID
------------------- ---------- ----------------

05/09/2005 17:16:50          3 06002100DFB30100
05/09/2005 17:16:50          4 06002100DFB30100
05/09/2005 17:16:50          3 06002100DFB30100
05/09/2005 17:16:50          2 06002100DFB30100
05/09/2005 17:16:50          1 06002100DFB30100
05/09/2005 17:16:50          0 06002100DFB30100
05/09/2005 17:16:50          4 06002100DFB30100
05/09/2005 17:16:50          3 06002100DFB30100
05/09/2005 17:16:50          0 06002100DFB30100
05/09/2005 17:16:50          3 06002100DFB30100
05/09/2005 17:16:50          4 06002100DFB30100
05/09/2005 17:16:50          2 06002100DFB30100
05/09/2005 17:16:50          4 06002100DFB30100
05/09/2005 17:16:50          0 06002100DFB30100
05/09/2005 17:16:50          4 06002100DFB30100
05/09/2005 17:16:50          0 06002100DFB30100
05/09/2005 17:16:50          4 06002100DFB30100
05/09/2005 17:16:50          3 06002100DFB30100
2005-03-23:20:32:29       2933
2005-04-06:21:44:19    1322450
2005-04-06:21:44:19    1322449
2005-04-06:21:44:19    1322448
2005-03-23:20:36:04    1274954
2005-03-23:20:36:04    1274953
2005-03-23:20:36:04    1274952
2005-04-08:17:27:56    1324530
2005-04-08:17:27:56    1324529
2003-05-06:13:27:35       6249
2003-05-06:13:27:34       6245
2003-05-06:13:06:17       2934
2003-05-06:13:06:17       2935
2003-05-06:13:06:17       2936
2003-05-06:13:06:17       2937
2003-05-06:13:06:18       2938
2003-05-06:13:06:18       2939
2003-05-06:13:11:14       3898

36 rows selected.

PB_at_t03a>
PB_at_t03a>
PB_at_t03a> select table_name, operation, undo_sql from flashback_transaction_=

query
2 where xid=3Dhextoraw('06002100DFB30100');

TABLE_NAME
---------------------------------------------------------------------------=

OPERATION

UNDO_SQL
---------------------------------------------------------------------------=

FOO
UPDATE
update "PB"."FOO" set "OBJECT_ID" =3D '2933', "TIMESTAMP" =3D '2005-03-23:2= 0:32:29'
where ROWID =3D 'AAFw4kAAOAAAHTNAAD';

BEGIN
--=20

Paul Baumgartel
paul.baumgartel_at_gmail.com
--

http://www.freelists.org/webpage/oracle-l Received on Mon May 09 2005 - 17:40:21 CDT