Application of the flash back function of Oracle 10g Query

InOracleIf the modification operation is submitted incorrectly,Then you want to view and modify the original value.,In this case, you can use the query to flash back.(Query flashback ).

You can query the flash back based on a time value or a system change number.(SCN: System Change Number)Proceed,Database usageSCNTo track changes to the data.,Therefore, it can be used to flash back to a specific databaseSCNStatus.

 

I.Perform the flash back operation,Need to useDBMS_FLASHBACKPackage,If you want to have this packageEXECUTEPermission,The following usesSysIdentity Logon,And authorize the userEXECUTEPermission:

CONNECT SYS/P @ ssw0rdAS SYSDBA;

GRANT EXECUTE ON SYS. DBMS_FLASHBACKTOUserName;

 

II.Flash back of Time query

1.RunSQLStatement fromProductsQuery the first five records,As follows::

SELECTProduct_id, name, price,SYSDATE AS TIME

FROMProducts

WHEREProduct_id <= 5;

2.Update record,As follows::

SELECTProduct_id,NAME, Price,SYSDATE AS TIME

FROMProducts

WHEREProduct_id <= 5;

3.RunDBMS_FLASHBACK.ENABLE_AT_TIME ()Statement,Returns to a specific time.,As follows::

DBMS_FLASHBACK.ENABLE_AT_TIME (SYSDATE-10/1440 );

Flash back10Minutes ago(24 h * 60 mins = 1440 Mins ).

4.Query now,The result is as follows:

5.Disable Flashback EXECUTE DBMS_FLASHBACK.DISABLE ();Before you enable the flashback operation again,You must disable it first..These commands can only beSqlplusUsed in,InPl/SQLCannot be used.

 

3..System Change number query flash back

AccordingSCNThe flash back operation is more accurate than the time-based operation.,Because the database is usedSCNTo track database changes.

1.Obtain the currentSCNCommand:

VARIABLECurr_scnNUMBER;

EXECUTE: Curr_scn: = DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER ();

Print curr_scn

2.Insert a record.

SELECT*FROMProducts;

INSERT INTOProducts (product_id, product_type_id,NAME,DESCRIPTION, Price)

VALUES(13, 5, 'kobe Bryant ', 'No. 24', '24. 00 ');

SELECT*

FROMProducts

WHEREProduct_id = 13;

3.PassDBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER ()The statement can return to thisSCNStatus,OneSCNParameters.

EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (: curr_scn );

4.Query the inserted data,No record.

SELECT*

FROMProducts

WHEREProduct_id = 13;

5.Disable Flashback: EXECUTE DBMS_FLASHBACK.DISABLE ();