The flashback table, in effect, is to quickly restore the data in the table to a previous point in time or to the system change number SCN. To implement the flash back of the table, you need to use Undo information related to the Undo tablespace, which can be learned through the show PARAMETER Undo command. User modifications to table data are recorded in the Undo table space, which provides the basis for data recovery provided by the table's flashback.
The default retention time for the modified record to be submitted to the undo Tablespace is 900 seconds, which allows the user to perform a flashback to the table in this 900-second time period, thus restoring the data in the table to the pre-modified state.
As shown by default of 900 seconds, we use SQL to modify this default time to 1200:
Syntax for flashtable:
Flashback table [schema.] table_name to {[Before drop [rename to New_table_name]] | [SCN | TIMESTAMP] expr [enable | desable] triggers}
Schema: Schema name table_name: Table name
Before drop indicates a reply to the previous delete
Rename New_table_name redefine table name
Timestamp timestamp, including date and time
Expr develop a value or expression
Enabled when the Enable triggers trigger is restored
Disabled triggers trigger replies, it is disabled, by default for this option.
The following shows an example:
First, create a table in the database and insert 3 data
Sql>create table TB_FLASH1 (ID number primary key,content varchar2 (40));
The table is created.
sql> INSERT INTO TB_FLASH1 (id,content)
2 Select 1, ' first row of data ' from dual
3 UNION ALL
4 Select 2, ' second row of data ' from dual
5 Union
6 Select 3, ' third row of data ' from dual
7/
3 rows have been created.
Sql> Commit
2/
Submit complete.
Query the data you just inserted:
Sql> SELECT * from TB_FLASH1
2/
ID CONTENT
---------- ----------------------------------------
1 First row of data
2 second row of data
3 third row of data
Suppose you now mistakenly delete one id=2 of data:
Sql> Delete from Tb_flash1 where id=2
2/
1 rows have been deleted.
Sql> commit;
Submit complete.
Query again, only two records are left in the table:
Sql> SELECT * from TB_FLASH1
2/
ID CONTENT
---------- ----------------------------------------
1 First row of data
3 third row of data
Next, perform a recovery operation on the data you just deleted:
1. Turn on the mobile function of the table
Sql> ALTER TABLE TB_FLASH1 enable row movement
2/
The table has changed.
2. Restore the table to the time when the data was deleted (note that the time is as close as possible to the time you deleted the data or slightly forward)
Sql> Flashback table Tb_flash1 to timestamp
2 To_timestamp (' 2013-01-26 15:58:30 ', ' yyyy-mm-dd hh24:mi:ss ')
3/
Flash back complete.
Sql> select * from TB_FLASH1;
ID CONTENT
---------- ----------------------------------------
1 First row of data
2 second row of data
3 third row of data
The data has been restored.
The following describes the revert to SCN
Converting timestamps to SCN
Sql> Select TIMESTAMP_TO_SCN (To_timestamp (' 2013-01-26 15:58:30 ', ' Yyyy-mm-dd hh24
: Mi:ss ')) from dual;
TIMESTAMP_TO_SCN (To_timestamp (' 2013-01-2615:58:30 ', ' yyyy-mm-ddhh24:mi:ss '))
---------------------------------------------------------------------------
1263136
Sql> Flashback table Tb_flash1 to 1263136;
Flash back complete. Flash back complete. Flash back complete.
One of Oracle's flashback Technologies Oracle 11g recovers (with delete) data mistakenly deleted using flashtable (flash back table)