|
|
|
|
|
|
| Re: FLASHBACK_TRANSACTION_QUERY.UNDO_SQL column [message #604553 is a reply to message #604548] |
Wed, 01 January 2014 02:19   |
|
|
SQL> drop table flashback_version_query_test;
Table dropped.
SQL> create table flashback_version_query_test(id number, descr varchar2(100));
Table created.
SQL> insert into flashback_version_query_test values(1, 'ONE');
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn,systimestamp from v$database;
CURRENT_SCN
-----------
SYSTIMESTAMP
---------------------------------------------------------------------------
4944430
01-JAN-14 02.02.41.215000 PM +06:00
SQL> update flashback_version_query_test set descr = 'TWO' where id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> update flashback_version_query_test set descr = 'THREE' WHERE id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select current_scn,systimestamp from v$database;
CURRENT_SCN
-----------
SYSTIMESTAMP
---------------------------------------------------------------------------
4944568
01-JAN-14 02.04.37.960000 PM +06:00
SQL> select descr from flashback_version_query_test
2 versions between scn 4944430 and 4944568;
DESCR
--------------------------------------------------------------------------------
THREE
TWO
ONE
SQL> select descr,versions_xid from flashback_version_query_test
2 versions between scn 4944430 and 4944568;
DESCR
--------------------------------------------------------------------------------
VERSIONS_XID
----------------
THREE
03000100EE0A0000
TWO
09000800CD0A0000
ONE
SQL> select xid, undo_sql from flashback_transaction_query
2 where xid = hextoraw('03000100EE0A0000');
XID
----------------
UNDO_SQL
--------------------------------------------------------------------------------
03000100EE0A0000
03000100EE0A0000
SQL> select xid, undo_sql from flashback_transaction_query
2 where xid = hextoraw('09000800CD0A0000');
XID
----------------
UNDO_SQL
--------------------------------------------------------------------------------
09000800CD0A0000
09000800CD0A0000
SQL> spool off
My confusion is in the output of last two SQL as there is nothing for column UNDO_SQL. Why this column is blank?
|
|
|
|
|
|
| Re: FLASHBACK_TRANSACTION_QUERY.UNDO_SQL column [message #604555 is a reply to message #604553] |
Wed, 01 January 2014 03:01 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
UNDO_SQL needs supplemental logging.
Without:
SQL> create table flashback_version_query_test(id number, descr varchar2(100));
Table created.
SQL> insert into flashback_version_query_test values(1, 'ONE');
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn,systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
----------- ---------------------------------------------------------------------------
37518738 01/01/2014 09:56:02.250 +01:00
1 row selected.
SQL> update flashback_version_query_test set descr = 'TWO' where id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> update flashback_version_query_test set descr = 'THREE' WHERE id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select current_scn,systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
----------- ---------------------------------------------------------------------------
37518754 01/01/2014 09:56:30.421 +01:00
1 row selected.
SQL> select descr,versions_xid from flashback_version_query_test
2 versions between scn 37518738 and 37518754;
DESCR VERSIONS_XID
---------------------------------------------------------------------------------------------------- ----------------
THREE 03001500A3410000
TWO 0A000A0019310000
ONE
3 rows selected.
SQL> select xid, undo_sql from flashback_transaction_query where xid = hextoraw('03001500A3410000');
XID
----------------
UNDO_SQL
-----------------------------------------------------------------------------------------------------------------------
03001500A3410000
03001500A3410000
2 rows selected.
SQL> drop table flashback_version_query_test;
Table dropped.
Add supplemental logging:
SQL> alter database add supplemental log data;
Database altered.
SQL> create table flashback_version_query_test(id number, descr varchar2(100));
Table created.
SQL> insert into flashback_version_query_test values(1, 'ONE');
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn,systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
----------- ---------------------------------------------------------------------------
37518850 01/01/2014 09:58:35.000 +01:00
1 row selected.
SQL> update flashback_version_query_test set descr = 'TWO' where id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> update flashback_version_query_test set descr = 'THREE' WHERE id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select current_scn,systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
----------- ---------------------------------------------------------------------------
37518861 01/01/2014 09:58:54.562 +01:00
1 row selected.
SQL> select descr,versions_xid from flashback_version_query_test versions between scn 37518850 and 37518861;
DESCR VERSIONS_XID
---------------------------------------------------------------------------------------------------- ----------------
THREE 09001C0083410000
TWO 050017003D410000
ONE
3 rows selected.
SQL> select xid, undo_sql from flashback_transaction_query where xid = hextoraw('09001C0083410000');
XID
----------------
UNDO_SQL
-----------------------------------------------------------------------------------------------------------------------
09001C0083410000
update "MICHEL"."FLASHBACK_VERSION_QUERY_TEST" set "DESCR" = 'TWO' where ROWID = 'AAAa+tAAEAAADeWAAA';
09001C0083410000
2 rows selected.
[Updated on: Wed, 01 January 2014 03:02] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
