FLASHBACK_TRANSACTION_QUERY: null values in UNDO_SQL and OPERATION is set to ‘UNKNOWN’

When executing a SELECT statement on FLASHBACK_TRANSACTION_QUERY gives you null values in UNDO_SQL and OPERATION is set to ‘UNKNOWN’.

The UNDO changes were not logged because of supplemental logging not enabled.

This is a change is there from 11g onwards. To make sure that the complete information is logged in the FLASHBACK_TRANSACTION_QUERY view, you must enable it.

Before:

XID              OPERATION
—————- ——————————–
UNDO_SQL
————————————————————————-

0A00100069020000 UNKNOWN

0A00100069020000 UNKNOWN

0A00100069020000 UNKNOWN

XID              OPERATION
—————- ——————————–
UNDO_SQL
————————————————————————-

0A00100069020000 UNKNOWN

After:

SQL> alter database add supplemental log data;

Database altered.

Perform some DML operations followed by COMMIT.

SQL>SELECT xid
,      operation
,      undo_sql
FROM   flashback_transaction_query
WHERE  table_owner = ‘MPOOJARI’
AND    table_name = ‘TEST’
ORDER  BY
start_timestamp;

XID              OPERATION
—————- ——————————–
UNDO_SQL
——————————————————————————–

02001D0008040000 INSERT
delete from “MPOOJARI”.”TEST” where ROWID = ‘AAARUxAAFAAAACEAAB’;

02001D0008040000 INSERT
delete from “MPOOJARI”.”TEST” where ROWID = ‘AAARUxAAFAAAACEAAA’;

02001D0008040000 UPDATE
update “MPOOJARI”.”TEST” set “NAME” = ‘Sathish’ where ROWID = ‘AAARUxAAFAAAACEAA

A’;

XID              OPERATION
—————- ——————————–
UNDO_SQL
——————————————————————————–

57 rows selected.

Reference:
http://forums.oracle.com/forums/thread.jspa?threadID=110738

Advertisements

About mpoojari
Oracle Database/Applications Administrator

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: