Oracle PL/SQL Tutorial/Transaction/Flashbacks

DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER() procedure enables you to perform a flashback to an SCN.

SQL>
SQL> VARIABLE current_scn NUMBER
SQL> EXECUTE :current_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
PL/SQL procedure successfully completed.
SQL> PRINT current_scn
CURRENT_SCN
-----------
    5547739
SQL>
SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:current_scn);
BEGIN DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:current_scn); END;

SQL>
--

Granting the Privilege for Using Flashbacks

The following example connects as the sys user and grants the EXECUTE privilege on DBMS_FLASHBACK to the store user:

CONNECT sys/change_on_install AS sysdba
GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO store;

Query Flashbacks

If you mistakenly commit changes and you want to view rows as they originally were, you can use a query flashback.

You can then use the results of a query flashback to manually change rows back to their original values if you need to.

In addition, flashbacks can be based on a datetime or system change number (SCN).

The database uses SCNs to track changes made to data, and you can use them to flash back to a particular SCN in the database.

35. 6. Flashbacks 35. 6. 1. Query Flashbacks 35. 6. 2. <A href="/Tutorial/Oracle/0680__Transaction/GrantingthePrivilegeforUsingFlashbacks.htm">Granting the Privilege for Using Flashbacks</a> 35. 6. 3. <A href="/Tutorial/Oracle/0680__Transaction/TimeQueryFlashbacks.htm">Time Query Flashbacks</a> 35. 6. 4. <A href="/Tutorial/Oracle/0680__Transaction/TodisableaflashbackyouexecuteDBMSFLASHBACKDISABLE.htm">To disable a flashback, you execute DBMS_FLASHBACK.DISABLE()</a> 35. 6. 5. <A href="/Tutorial/Oracle/0680__Transaction/SystemChangeNumberQueryFlashbacks.htm">System Change Number Query Flashbacks</a> 35. 6. 6. <A href="/Tutorial/Oracle/0680__Transaction/DBMSFLASHBACKENABLEATSYSTEMCHANGENUMBERprocedureenablesyoutoperformaflashbacktoanSCN.htm">DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER() procedure enables you to perform a flashback to an SCN.</a>

System Change Number Query Flashbacks

Flashbacks based on system change numbers (SCNs) can be more precise than those based on a time, because the database uses SCNs to track changes.

SQL> VARIABLE current_scn NUMBER
SQL> EXECUTE :current_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
PL/SQL procedure successfully completed.
SQL> PRINT current_scn
CURRENT_SCN
-----------
    5547739
SQL>

Time Query Flashbacks

SQL>
SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE - 10 / 1440);
BEGIN DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE - 10 / 1440); END;

To disable a flashback, you execute DBMS_FLASHBACK.DISABLE()

SQL>
SQL> EXECUTE DBMS_FLASHBACK.DISABLE();
PL/SQL procedure successfully completed.

You must disable a flashback before you can enable it again.