flashback_transaction_query
- From: Paul Baumgartel <paul.baumgartel@xxxxxxxxx>
- To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 9 May 2005 17:34:54 -0400
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@t03a> select current_scn from v$database;
CURRENT_SCN
-----------
353809838
PB@t03a> update foo set timestamp=3Dsysdate,object_id=3Dmod(object_id,5)
where owner =3D'SYSTEM';
18 rows updated.
PB@t03a> commit;
Commit complete.
PB@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@t03a>
PB@t03a>
PB@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@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » flashback_transaction_query
