Use of Oracle Flashback table

Use of Oracle Flashback table

Oracle ensures that recyclebin is enabled

SQL> show parameter recyclebin

NAME TYPE VALUE
-----------------------------------------------------------------------------
Recyclebin string ON creates a table

SQL> create table tab01 (id int );

Table created.

SQL> insert into tab01 values (1 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tab01;

ID
----------
1

SQL> create index ind_id on tab01 (id );

Index created. Delete table TAB01

18:18:26 SQL> select index_name from ind where table_name = 'tab01 ';

INDEX_NAME
------------------------------
IND_ID

18:18:33 SQL> drop table tab01;

Table dropped.

18:18:41 SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-----------------------------------------------------------------------------
TAB01 BIN $ 7E8nf4eZQZzgQKjACzgfMg ==$ 0 TABLE 2013-11-29: 18: 18: 41
18:18:43 SQL> select index_name from ind where table_name = 'tab01 ';

No rows selected

18:18:50 SQL> select * from tab01;
Select * from tab01
*
ERROR at line 1:
ORA-00942: table or view does not exist
It is found that the index on TAB01 is also rename, flashback TAB01

18:19:41 SQL> flashback table tab01 to before drop;

Flashback complete.

18:19:51 SQL> select * from tab01;

ID
----------
1

18:19:54 SQL> select index_name from ind where table_name = 'tab01 ';

INDEX_NAME
------------------------------
BIN $ 7E8nf4eYQZzgQKjACzgfMg = $0 rename index

18:23:09 SQL> ALTER INDEX "BIN $ 7E8nf4eYQZzgQKjACzgfMg = $0" RENAME TO IDX_ID;

Index altered.

18:23:45 SQL> select index_name, status from ind where table_name = 'tab01 ';

INDEX_NAME STATUS
--------------------------------------
IDX_ID VALID

 

If you delete the same table multiple times, you can also specify the recyclebin name flashback.

18:25:29 SQL> select * from tab01;

ID
----------
1

18:25:36 SQL> drop table tab01;

Table dropped.

At 18:25:50 SQL> create table tab01 (id int );

Table created.

18:26:17 SQL> insert into tab01 values (2 );

1 row created.

18:26:30 SQL> commit;

Commit complete.

18:26:33 SQL> select * from tab01;

ID
----------
2

18:26:37 SQL> drop table tab01;

Table dropped.

At 18:26:43 SQL> create table tab01 (id int );

Table created.

18:26:46 SQL> insert into tab01 values (3 );

1 row created.

18:26:55 SQL> commit;

Commit complete.

18:26:56 SQL> select * from tab01;

ID
----------
3

18:26:59 SQL> drop table tab01;

Table dropped.

18:27:02 SQL> select * from tab01;
Select * from tab01
*
ERROR at line 1:
ORA-00942: table or view does not exist18: 27: 10 SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-----------------------------------------------------------------------------
TAB01 BIN $ 7E8nf4edQZzgQKjACzgfMg ==$ 0 TABLE 2013-11-29: 18: 27: 02
TAB01 BIN $ 7E8nf4ecQZzgQKjACzgfMg ==$ 0 TABLE 2013-11-29: 18: 26: 43
TAB01 BIN $ 7E8nf4ebQZzgQKjACzgfMg = $0 TABLE 2013-11-29: 18: 25: 50 flashback

18:27:51 SQL> flashback table "BIN $ 7E8nf4ecQZzgQKjACzgfMg = $0" to before drop;

Flashback complete.

18:29:17 SQL> select * from tab01;

ID
----------
2
Flashback and rename

18:30:54 SQL> flashback table "BIN $ 7E8nf4edQZzgQKjACzgfMg = $0" to before drop rename to tab02;

Flashback complete.

18:31:17 SQL> select * from tab02;

ID
----------
3
You can also perform table-level time-point-based recovery based on timestamp or scn. You need to enable row movement.

At 18:32:42 SQL> create table tab03 (id int );

Table created.

18:32:55 SQL> insert into tab03 values (1 );

1 row created.

18:33:08 SQL> insert into tab03 values (2 );

1 row created.

18:33:10 SQL> insert into tab03 values (3 );

1 row created.

18:33:12 SQL> commit;

Commit complete.

18:33:14 SQL>
18:33:16 SQL> insert into tab03 values (4 );

1 row created.

18:33:23 SQL> commit;

Commit complete.

18:33:25 SQL> select * from tab03;

ID
----------
1
2
3
418: 35: 33 SQL> FLASHBACK TABLE TAB03 TO timestamp to_timestamp ('2017-11-29 18:33:25 ', 'yyyy-mm-dd hh24: mi: ss ');

Flashback complete.

18:35:39 SQL> select * from tab03;

ID
----------
1
2
3

18:35:40 SQL> FLASHBACK TABLE TAB03 TO timestamp to_timestamp ('2017-11-29 18:33:30 ', 'yyyy-mm-dd hh24: mi: ss ');

Flashback complete.

18:35:54 SQL> select * from tab03;

ID
----------
1
2
3
4

18:35:55 SQL> FLASHBACK TABLE TAB03 TO timestamp to_timestamp ('2017-11-29 18:33:25 ', 'yyyy-mm-dd hh24: mi: ss ');

Flashback complete.

18:35:59 SQL> select * from tab03;

ID
----------
1
2
3

Oracle 11g Flashback Data Archive (flash back Data archiving)

Oracle Flashback flash back Mechanism

Oracle Flashback database

Flashback table quick recovery of accidentally deleted data

Oracle backup recovery: Flashback flash back