Oracle 11g uses flashtable (Flash return table) to recover (with delete) the data mistakenly deleted __oracle

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 SCN. To implement the flashback of the table, you need to use the Undo information related to the Undo table space, and you can understand this information by show PARAMETER Undo command. The user's modification of the table's data is recorded in the Undo Tablespace, which provides the basis for the data recovery provided by the table's flash-back.

Modified records are submitted to the undo tablespace with a default retention time of 900 seconds in which the user can flash back the table for a period of 900 seconds, thereby restoring the data in the table to the state before the modification.

As the default 900 seconds shown in the figure above, we modify this default time by SQL 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 means revert to before deletion

Rename New_table_name redefine table name

Timestamp time stamp, including month-day time and seconds

Expr develop a value or expression

Enable triggers triggers are enabled after recovery

Disabled triggers trigger reply is disabled, by default 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));

Table has been created.

sql> INSERT INTO TB_FLASH1 (id,content)
2 Select 1, ' First row data ' from dual
3 UNION ALL
4 Select 2, ' second row data ' from dual
5 Union
6 Select 3, ' third row data ' from dual
7/

3 lines have been created.

Sql> Commit
2/

Submit completed.

Query the data you just inserted:

Sql> SELECT * from TB_FLASH1
2/

ID CONTENT
---------- ----------------------------------------
1 The first line of data
2 second line of data
3 Third row data

Suppose you now mistakenly delete a id=2 data:

Sql> Delete from Tb_flash1 where id=2
2/

1 rows have been deleted.

Sql> commit;

Submit completed.

Once again, there are only two entries left in the table:

Sql> SELECT * from TB_FLASH1
2/

ID CONTENT
---------- ----------------------------------------
1 The first line of data
3 Third row data

Next, perform a restore operation on the data that you just deleted:

1, open the table mobile function

Sql> ALTER TABLE TB_FLASH1 enable row movement
2/

The table has changed.

2, restore the table before the time to delete data (note that the time as much as you delete the data with the same time or slightly ahead)

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 The first line of data
2 second line of data
3 Third row data

The data has been recovered.

The following is a resume to SCN

Convert Timestamp 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.