Oracle PL/SQL Tutorial/Transaction/Flashbacks

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

   <source lang="sql">

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> --</source>

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:

   <source lang="sql">

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

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.

   <source lang="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></source>

Time Query Flashbacks

   <source lang="sql">

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

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

   <source lang="sql">

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

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