Oracle restores the deleted table from the recycle bin

In Versions later than Oracle10g, the concept of quot; Recycle BIN quot; (RecycleBin) is introduced. The deleted objects are all put into the recycle BIN and named with the BIN $ prefix. The user deletes

The concept of "Recycle Bin" (Recycle BIN) is introduced in Oracle Versions later than 10 Gb. The deleted objects are put in the Recycle Bin and named with the BIN $ prefix.

The "Recycle Bin" (Recycle BIN) concept is introduced in Oracle Versions later than 10 Gb. The deleted objects are all placed in the Recycle Bin and named with the BIN $ prefix. A user can easily restore a table from the "recycle bin", but before 9i, DDL operations such as table deletion cannot be restored directly, usually Incomplete recovery is required or EXP/IMP is used for restoration.

1. Check whether the current recycle bin function is enabled (enabled by default)

SQL> column value format a10;
SQL> select value from V $ parameter where name = 'recyclebin ';

VALUE
----------
On

You can use the following command to enable or disable this function.

SQL> alter system set recyclebin = on scope = spfile;
Or
SQL> alter session set recyclebin = on scope = spfile;

SQL> alter system set recyclebin = off scope = spfile;
Or
SQL> alter session set recyclebin = off scope = spfile;

2. view the content in the recycle bin

SQL> show recyclebin;

Or
SQL> select * from user_recyclebin;

Delete the created table and put it in the recycle bin.

SQL> show user;
USER is "HXL"
SQL> create table test_rbin (val number );

Table created.

SQL> insert into test_rbin (val) values (10 );

1 row created.

SQL> commit;

Commit complete.

SQL> drop table test_rbin;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-----------------------------------------------------------------------------

TEST_RBIN BIN $ S4u3UNw0QQ6t6LRxMMz7hQ ==$ 0 TABLE 2012-06-22: 15: 01: 30

3. Restore the deleted table from the recycle bin

You can use the following statement to restore the deleted table from the recycle bin:

SQL> flashback table < > To before drop;

For example, we can restore the table we just deleted.

SQL> flashback table test_rbin to before drop;

Flashback complete.

SQL> select * from test_rbin;

VAL
----------
10

During the restoration process, we can name the table separately. The command is as follows:

SQL> flashback table <dropped table name> to before drop rename to < >;
SQL> flashback table test_rbin to before drop rename to test_rbin1;

Oracle recovery from the recycle bin is performed in descending order. For example, you can delete the same table three times in a row (after the table is deleted, it is created and deleted ), the last deleted table is restored first.

SQL> show user;
USER is "HXL"
SQL> drop table test_rbin;

Table dropped.

SQL> create table test_rbin (col1 number );

Table created.

SQL> insert into test_rbin values (1 );

1 row created.

SQL> commit;

Commit complete.

SQL> drop table test_rbin;

Table dropped.

SQL> create table test_rbin (col1 number );

Table created.

SQL> insert into test_rbin values (2 );

1 row created.

SQL> commit;

Commit complete.

SQL> drop table test_rbin;

Table dropped.

SQL> create table test_rbin (col1 number );

Table created.

SQL> insert into test_rbin values (3 );

1 row created.

SQL> commit;

Commit complete.

SQL> drop table test_rbin;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-----------------------------------------------------------------------------

TEST_RBIN BIN $ gGHkV/xqssaq5bg0pik1_===$ 0 TABLE 2012-06-22: 15: 31: 21
TEST_RBIN BIN $ xVKygKwJTJa + 8zmu4BJzvQ ===$ 0 TABLE 2012-06-22: 15: 30: 53
TEST_RBIN BIN $ R7As9PsYRva7CY6cnAjROw = $0 TABLE 2012-06-22: 15: 30: 23
TEST_RBIN BIN $ 0R + cRKhDTFu + 8 ShBjLDpqg ==$ 0 TABLE 2012-06-22: 15: 29: 27

SQL> select * from test_rbin1;

COL1
----------
3

SQL> select * from test_rbin2;

COL1
----------
2

SQL> select * from test_rbin3;

COL1
----------
1

,