FLASHBACK_TRANSACTION_QUERY gives you null values in UNDO_SQL and OPERATION is set to ‘UNKNOWN

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_create_file_dest                  string      D:\romford\oradata
db_recovery_file_dest                string      D:\romford\flash_recovery_area
db_recovery_file_dest_size           big integer 3G
db_flashback_retention_target        integer     1440
recyclebin                           string      on
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> alter tablespace UNDOTBS1 retention guarantee;

Tablespace altered.

SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME                RETENTION
—————————— ———–
SYSTEM                         NOT APPLY
SYSAUX                         NOT APPLY
UNDOTBS1                       GUARANTEE
TEMP                           NOT APPLY
USERS                          NOT APPLY
MPOOJARI                       NOT APPLY

To flash back a table to an earlier SCN or timestamp, you must have either the

1. FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege.
2. SELECT, INSERT, DELETE, and ALTER object privileges on the table.
3. Row movement must be enabled for all tables in the Flashback list

Oracle has provided a new view, FLASHBACK_TRANSACTION_QUERY, to provide more information about the data versions. This includes the SQL required to reverse each change. Queries against this view are documented as “flashback transaction queries” and require the SELECT ANY TRANSACTION system privilege.

Error: Selecting from FLASHBACK_TRANSACTION_QUERY gives you null values in UNDO_SQL and OPERATION is set to ‘UNKNOWN’.

Its not that there wasn’t sufficient undo but it was not logged because of supplemental logging not enabled. This is a change which is there from 11g onwards. To make sure that the completel information is logged in the FLASHBACK_TRANSACTION_QUERY view, you must enable it. You can do so by,

SQL> alter database add supplemental log data;

Database altered.

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: