20.3 use flashback table to restore the deleted table

3.1 database Recycle Bin

Database recycle bin is the container for all deleted tables and related objects, objects stored in the recycle bin include indexes, constraints, triggers, nested tables, lob segments, and LOB index segments.

The database Recycle Bin has the following restrictions:

• The recycle bin is only applicable to non-system local tablespace management.
• Oracle does not allocate a fixed reserved space for the recycle bin. Therefore, the retention time of database objects in the recycle bin cannot be guaranteed. When the tablespace of the deleted object does not have enough space, Oracle uses the FIFO (first-in-first-out) mechanism to clear the corresponding objects in the recycle bin.
• The SELECT statement can be used to query the data of the recycle bin object, but DML and DDL operations cannot be performed on the Recycle Bin object.

 

1. Generate the Recycle Bin object

SQL> conn Scott/tiger @ demo
Connected.
SQL> Create Table T1 (C INT );

The table has been created.

SQL> Create index ix_t1 on T1 (C );

The index has been created.

SQL> Create Table T2 (C INT );

The table has been created.

SQL> Create index ix_t2 on T2 (C );

The index has been created.

SQL> Create Table T3 (C INT );

The table has been created.

SQL> Create index ix_t3 on T3 (C );

The index has been created.

SQL> drop table T1;

The table has been deleted.

SQL> drop table T2;

The table has been deleted.

SQL> drop table T3 purge;

The table has been deleted.

2. display the current user's Recycle Bin object

SQL> show recyclebin
Original Name recyclebin name object type drop time
-----------------------------------------------------------------------------

T1 bin $ frfdmiaiqnspx16x7b3vw = $0 table 2009-02-19: 22: 53: 03

T2 bin $ tzjkr3xatvydcpbhzzr3hg = $0 table 2009-02-19: 22: 53: 04

SQL> select object_name, original_name from recyclebin;

Object_name original_name
--------------------------------------------------------------
Bin $ d4mvz2uispk13svwadpnwg ==$ 0 ix_t1
Bin $ tzjkr3xatvydcpbhzzr3hg = $0 T2
Bin $ frfdmiaiqnspx16x7b3vw = $0 T1
Bin $ kr3toqkcq8cv3cwiubrjfq = $0 ix_t2

3. display all recycle bin objects of the database

SQL> Conn/As sysdba
Connected.
SQL> select owner, object_name, original_name from dba_recyclebin;

Owner object_name original_name
--------------------------------------------------------------------
Scott bin $ d4mvz2uispk13svwadpnwg ==$ 0 ix_t1
Scott bin $ tzjkr3xatvydcpbhzzr3hg ===$ 0 T2
Scott bin $ frfdmiaiqnspx16x7b3vw ==$ 0 T1
Scott bin $ kr3toqkcq8cv3cwiubrjfq ===$ 0 ix_t2

4. Clear the Recycle Bin object

SQL> conn Scott/tiger @ demo
Connected.
SQL> purge table T1;

The table has been cleared.

SQL> purge index ix_t2;

The index has been cleared.

SQL> purge recyclebin;

The Recycle Bin has been cleared.

 

Lifecycle 3.2 restore the deleted table

(1) simulate user misoperations

(2) display the Recycle Bin object

(3) restore the deleted table

SQL> conn Scott/Tiger
Connected.
SQL> show recyclebin
SQL> drop table EMP;

The table has been deleted.

SQL> show recyclebin
Original Name recyclebin name object type drop time
-----------------------------------------------------------------------------

EMP bin $/3zsvld5rhwzuasougw64a = $0 table: 23: 10: 46

SQL> flashback table "bin $/3zsvld5rhwzuasougw64a = $0" to before drop Rename to newemp;

Flash back complete.

SQL> drop table newemp;

The table has been deleted.

SQL> show recyclebin
Original Name recyclebin name object type drop time
-----------------------------------------------------------------------------

Newemp bin $ cqgc0n8 + qjk/y9ofvld74w = $0 table 2009-02-19: 23: 13: 11

SQL> flashback table newemp to before drop Rename to EMP;

Flash back complete.