Oracle Flash Back Technology

Undo data is retained for 7 days
Orcl> Show Parameter Undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_undo_cost_change string 12.1.0.2
temp_undo_enabled Boolean FALSE
Undo_management string AUTO
Undo_retention integer 604800
Undo_tablespace string UNDOTBS1
Orcl> select 604800/60/60/24 from dual;

604800/60/60/24
---------------
7

1: Flash back query, using Undo

Querying table data at a specified point in time

SELECT * from Scott.acct as of timestamp to_timestamp (' 2018-05-20 15:30:00 ', ' yyyy-mm-dd hh24:mi:ss ') WHERE user_id = 1;

2: Flash Back table

The Flashback table (Flashback tables) uses the Undo information to restore a Table object to a previous point in time (a snapshot)

Sql>flashback table test to timestamp to_timestamp (' 2018-05-20 15:30:00 ', ' yyyy-mm-dd hh24:mi:ss ');

Sql>flashback table test to timestamp SCN 115544;

Sql>flashback table test to timestamp to_timestamp (' 2018-05-20 15:30:00 ', ' yyyy-mm-dd hh24:mi:ss ') enable triggers;

Using the Flashback table premise

1) The normal user needs to have flashback any table system permissions. Commands such as:

SQL>Grant Flashback any table to Scott;

2) There is a SELECT, INSERT, delete, ALTER permission for the table.

3) The table must be guaranteed to have row movement (row movement).

Restore the table to a certain point in the past, restore the test table to just recorded point in time (or SCN), because there are triggers in the table, so the use of the keyword enable triggers;

Flashback table Test to timestamp to_timestamp (' 2018-05-20 15:30:00 ', ' yyyy-mm-dd hh24:mi:ss ') enable triggers;

3: Flash Back Data archive

Querying the state of a past period of time

Select
VERSIONS_STARTSCN, VERSIONS_ENDSCN, Versions_starttime, Versions_endtime, Versions_xid, Versions_operation,ORDER_ID
From Scott.emp
WHERE order_id in (100827979,100827973,100785947);

Oracle Flash Back Technology