One of Oracle's flashback Technologies Oracle 11g recovers (with delete) data mistakenly deleted using flashtable (flash back table)

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)