Search code examples
oracleflashback

Flashback query retention time

I want to set flashback query retention to 240 in order to be able to perform flashback query from last 4 hours.

I would like to do something similar to this: 

alter system set db_flashback_retention_target = 240;

but I couldn't find the name of the parameter responsible for flashback query retention time.

Solution

  • This would be the UNDO_RETENTION parameter, as flashback queries use UNDO mechanism.

    UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

    The setting of this parameter should account for any flashback requirements of the system. Automatic tuning of undo retention is not supported for LOBs. The RETENTION value for LOB columns is set to the value of the UNDO_RETENTION parameter.