Home > Oracle > Flashback version query – OPERATION is UNKNOWN

Flashback version query – OPERATION is UNKNOWN


I was testing Flashback Version Query and stuck with OPERATION column in FLASHBACK_TRANSACTION_QUERY – I was getting UNKNOWN for all operations.

SQL> select START_SCN,OPERATION,LOGON_USER,XID
from flashback_transaction_query where TABLE_NAME=’TEST’;

 START_SCN OPERATION                        LOGON_USER                     XID
———- ——————————– —————————— —————-
  10868999 UNKNOWN                          USER1                          0002000B0000064E
  10866463 UNKNOWN                          USER1                          0002000900000651

Also, I was not getting the undo_sql, returns empty – simple problem, but reason for a google search.

I enabled the supplemental logging, that resolved my problem.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ;

Database altered.
SQL> update test set salary=1000;

6 rows updated.
SQL> select START_SCN,OPERATION,LOGON_USER,XID from flashback_transaction_query where TABLE_NAME=’TEST’;
 START_SCN OPERATION                        LOGON_USER                     XID
———- ——————————– —————————— —————-
  10868999 UNKNOWN                          USER1                          0002000B0000064E
  10866463 UNKNOWN                          USER1                          0002000900000651
  10869230 UPDATE                           USER1                          0009000F0000063F
  10869230 UPDATE                           USER1                          0009000F0000063F
  10869230 UPDATE                           USER1                          0009000F0000063F
  10869230 UPDATE                           USER1                          0009000F0000063F
  10869230 UPDATE                           USER1                          0009000F0000063F
  10869230 UPDATE                           USER1                          0009000F0000063F

8 rows selected.

SQL> SQL> select UNDO_SQL  from flashback_transaction_query where TABLE_NAME=’TEST’;

UNDO_SQL
——————————————————————————
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAG’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAF’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAE’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAD’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAC’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAB’;

8 rows selected.

But the old entries still shown unknown.  Also, note that there is only one record in the flashback_transaction_query  regrdless the number of records got updated.  So, enabling SUPPLEMENTAL LOG DATA is (My test case was in 11gR2) a pre-requsite for playing with flashback version query.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

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: