An introduction to Flashback Flash-back in Oracle

1, must set the undo retention time is large enough to be able to reconstruct the required flash back data

The ALTER SYSTEM SET undo_retention= seconds value is the number of seconds that the UNDO data is persisted.

Flashback view is limited by the undo retention interval.

2, the package Dbms_flashback provides the demand interface

Call Dbms_flashback.enable_at_time (' 2010-10-19:11:00:00 ');

Call Dbms_flashback.disable ();

-------------------------------------

Enable_at_time: Session-level enable flashback, the image time is set to the SCN that is closest to the specified timestamp

Enable_at_system_change_number: Flashes the database back to the specified SCN number.

Get_system_change_number: Returns the current SCN.

Disable: This stored procedure allows us to stop flashback throughout the session and bring you back to the data state of the current time.

----------

The dbms_flashback.enable stored procedure cannot be executed when there is an active transaction, and the package cannot be executed with the SYS identity.

In the use of dbms_flashback. Before Enable_at_time, you must set the exact level of your Nls_date_format, Oracle default is accurate to the day

3, timestamp and SCN (System change number) of the corresponding relationship

In fact, Oracle uses SCN internally, even if you specify as of Timestamp,oracle it will be converted to SCN, there is a table between the system time tag and the SCN, that is, the Smon_scn_time table under sys.

Every 5 minutes, the system produces a match between the system time tag and the SCN and stores the Sys.smon_scn_time table, which records the matching record of the last 1440 system time markers to the SCN, because the table only maintains the most recent 1440 records, so if you use the AS The timestamp method can only flashback the data in the last 5 days (assuming that the system is running continuously without interruption or shutdown restart, and so on). SYS. Smon_scn_time has a maximum of 1440 records. This maximum number of records is computed in this way, and Oracle synchronizes the table data every 5 minutes, keeping records for the most recent 5 days, and thus the equivalent of 12 (the number of updates per hour) *24*5=1440.

You can verify with sql:

SQL code

Select Scn,to_char (TIME_DP, ' Yyyy-mm-dd hh24:mi:ss ') from Sys.smon_scn_time;

Two functions Scn_to_timestamp () and TIMESTAMP_TO_SCN () are provided in 10g to perform the timestamp and SCN conversions.

Here's a flash-back demo:

1, landing to the database.

SQL code

C:>sqlplus Tivan/tivan

Sql*plus:release 10.2.0.1.0-production on Tuesday October 19 22:24:03 2010

Copyright (c) 1982, +, Oracle. All rights reserved.

Connect to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production

With the partitioning, OLAP and Data Mining options

2, view the record of the table.

SQL code

Sql> Select COUNT (*) from T1

2;

COUNT (*)

----------

8302

3, delete all the records submitted.

SQL code

sql> Delete from T1

2;

8302 rows have been deleted.

Sql> commit;

Submit completed.

4. Get current SCN

If you can know exactly what the SCN is best before removing it, you can try the flashback query if you don't know.

SQL code

Sql> select Dbms_flashback.get_system_change_number from dual;

Get_system_change_number

------------------------

1482649

Sql> Select COUNT (*) from T1 as of SCN 1482600;

COUNT (*)

----------

8302

When scn=1482600, all the records in the T1 table are still in.

5, the use of flash-back recovery data.

Check the verification:

SQL code

sql> INSERT INTO T1 SELECT * from T1 as of SCN 1482600;

8302 lines have been created.

Sql> commit;

Submit completed.

Result OK

Or you can do the following

SQL code

Sql>flashback table T1 to SCN 1482600;

Sql> commit;

Commit complete.

--For the drop table T1 operation flashback

sql> drop table T1;

sql>commit;

Sql> Flashback table t1to before drop;

sql>commit;

The TIMESTAMP_TO_SCN () function can be used for how the SCN is fetched, such as:

SQL code

Select TIMESTAMP_TO_SCN (To_timestamp (' 2010-10-19 21:00:00 ', ' yyyy-mm-dd HH:MI:SS ')) from dual;