Flashback Queries in PLSQL

Flashback queries let you view and repair historical data. It queries the database by TIME or user specified System Change Number (SCN). Every DML statement has SCN attached to it. It uses oracles multi version read-consistency capabilities to restore data by applying UNDO as needed.

You can configure UNDO retention by simply specifying how long UNDO should be kept in the database. Flashback queries don’t perform undo operation. You can take the output from a flashback query and perform an UNDO yourself. It does not tell you what has UNDO. You can get the undo content by using LOGMINER package of oracle.

Flashback query does not work with DDL operations that modify columns and dropped or truncated tables.

Setting the time of flashback and retrieving data before 5 minutes, i.e. 5/1440 (1440 is the number of minutes in a day)

Execute dbms_flashback.enable_at_time(sysdate – 5/1440);

You cannot insert values in the table in the flashback mode. Now disable the dbms_flashback package.

Execute dbms_flashback.disable;

The DBMS_FLASBACK.GET_SYSTEM_CHANGE_NUMBER property gives SCN.

Begin

Dbms_flashback.enable_at_system_change_number(3997981);

End;

Begin

Dbms_flashback.disable;

End;