Oracle Recycle Bin回收站

概述：oracle回收站的功能是从oracle10g开始新增的，回收站故名思议就是表或者其他某些对象删除后存放的地方；这里对回收站功能进行简单的测试记录。

实验：

实验准备

SQL> create user user1 identified by oracle;
User created.
SQL> create user user2 identified by oracle;
User created.
SQL> grant connect,resource,unlimited tablespace to user1;
Grant succeeded.
SQL> grant connect,resource,unlimited tablespace to user2;
Grant succeeded.
SQL> conn user1/oracle

Connected.

SQL> create table test1 (id int);
Table created.

SQL> conn user2/oracle
Connected.
SQL> create table test1 (id int);

Table created.

查看回收站功能是否打开：

SQL> show parameter recycle
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle                  string
db_recycle_cache_size                big integer 0

recyclebin                           string      on

SQL> conn user2/oracle
Connected.
SQL> ALTER SESSION SET recyclebin = OFF;
Session altered.
SQL> drop table test1;
Table dropped.
SQL> show RECYCLEBIN;
SQL> conn user1/oracle
Connected.
SQL> drop table test1;
Table dropped.
SQL> show RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1            BIN$beEmfBKACXHgU8gCAMBB+Q==$0 TABLE        2018-06-05:15:39:29
SQL> SELECT object_name, original_name FROM dba_recyclebin WHERE owner = 'USER1';
OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$beEmfBKACXHgU8gCAMBB+Q==$0 TEST1
SQL> SELECT object_name, original_name FROM dba_recyclebin WHERE owner = 'USER2';

no rows selected

 create  table test2 (id int);（user1、user2创建测试表）

SQL> alter system set recyclebin=off;
alter system set recyclebin=off
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option

SQL> alter system set recyclebin=off scope=spfile;
System altered.

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  776646656 bytes
Fixed Size                  2257272 bytes
Variable Size             478154376 bytes
Database Buffers          293601280 bytes
Redo Buffers                2633728 bytes
Database mounted.

Database opened.

SQL> conn user1/oracle
Connected.
SQL> ALTER SESSION SET recyclebin = on;
Session altered.
SQL> drop table test2;
Table dropped.
SQL>  show RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1            BIN$beEmfBKACXHgU8gCAMBB+Q==$0 TABLE        2018-06-05:15:39:29

TEST2            BIN$beE9VB7SCm3gU8gCAMB1qg==$0 TABLE        2018-06-05:15:45:52

SQL> conn user2/oracle
Connected.
SQL> drop table test2;
Table dropped.

SQL>  show RECYCLEBIN;

1、系统基本修改回收站的开启关闭功能需要重启实例生效，session级别的回收站功能要强于system级别，一般推荐开启回收站功能并且数据库默认就是开启的。

SQL> conn user1/oracle
Connected.
SQL>  create table test3 (id int);

Table created.

SQL> conn user2/oracle
Connected.
SQL> create table test3 (id int,name varchar2(20));

Table created.

SQL> conn user1/oracle
Connected.
SQL> drop table test3 purge;
Table dropped.
SQL> show RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1            BIN$beEmfBKACXHgU8gCAMBB+Q==$0 TABLE        2018-06-05:15:39:29

TEST2            BIN$beE9VB7SCm3gU8gCAMB1qg==$0 TABLE        2018-06-05:15:45:52

SQL> conn user2/oracle
Connected.
SQL> drop table test3;
Table dropped.
SQL> show RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------

TEST3            BIN$beFZ2+5jCuvgU8gCAMB1Mg==$0 TABLE        2018-06-05:15:53:51

SQL> purge table test3;
Table purged.

SQL> show recyclebin;

如果删除表时不需要将此表存储在回收站中可在删除表后面添加purge，或者也可以在删除表后进行purge table进行清除回收站；

SQL> conn user1/oracle
Connected.
SQL> create table test4 (id int);
Table created.
SQL> drop table test4;
Table dropped.
SQL> create table test4 (id int);
Table created.
SQL> drop table test4;
Table dropped.
SQL>  create table test4 (id int);
Table created.
SQL> drop table test4;
Table dropped.
SQL> show RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1            BIN$beEmfBKACXHgU8gCAMBB+Q==$0 TABLE        2018-06-05:15:39:29
TEST2            BIN$beE9VB7SCm3gU8gCAMB1qg==$0 TABLE        2018-06-05:15:45:52
TEST4            BIN$beFnjBaNCwLgU8gCAMBI1g==$0 TABLE        2018-06-05:15:57:57
TEST4            BIN$beFnjBaMCwLgU8gCAMBI1g==$0 TABLE        2018-06-05:15:57:49

TEST4            BIN$beFnjBaLCwLgU8gCAMBI1g==$0 TABLE        2018-06-05:15:57:40

SQL> purge table test4;
Table purged.
SQL> show RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1            BIN$beEmfBKACXHgU8gCAMBB+Q==$0 TABLE        2018-06-05:15:39:29
TEST2            BIN$beE9VB7SCm3gU8gCAMB1qg==$0 TABLE        2018-06-05:15:45:52
TEST4            BIN$beFnjBaNCwLgU8gCAMBI1g==$0 TABLE        2018-06-05:15:57:57
TEST4            BIN$beFnjBaMCwLgU8gCAMBI1g==$0 TABLE        2018-06-05:15:57:49
SQL> purge table test4;
Table purged.
SQL> show RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1            BIN$beEmfBKACXHgU8gCAMBB+Q==$0 TABLE        2018-06-05:15:39:29
TEST2            BIN$beE9VB7SCm3gU8gCAMB1qg==$0 TABLE        2018-06-05:15:45:52

TEST4            BIN$beFnjBaNCwLgU8gCAMBI1g==$0 TABLE        2018-06-05:15:57:57

回收站purge table时遵循：先进先出的原则进行回收；

SQL> SELECT object_name, original_name FROM dba_recyclebin WHERE owner = 'USER2';
OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$beF49g+WCzTgU8gCAMBjsA==$0 TEST4
SQL> SELECT object_name, original_name FROM dba_recyclebin WHERE owner = 'USER1';
OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$beFnjBaNCwLgU8gCAMBI1g==$0 TEST4
BIN$beE9VB7SCm3gU8gCAMB1qg==$0 TEST2

BIN$beEmfBKACXHgU8gCAMBB+Q==$0 TEST1

SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> SELECT object_name, original_name FROM dba_recyclebin WHERE owner = 'USER1';
no rows selected
SQL>  SELECT object_name, original_name FROM dba_recyclebin WHERE owner = 'USER2';

no rows selected

每个用户都有自己独立的回收站，当dba需要定期清除全部用户下的回收站时可使用purge dba_recyclebin;进行清除

purge还可以针对表空间级purge tablespace user1进行清理，也可以在自己的schema下进行purge recyclebin进行清理；

正常的删除表后其实只是将表放在了回收站中，在满足一定的条件情况下，其实可以将表从回收站中进行恢复的。可以参考官方文档进行操作：https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN01506

总结：回收站功能请开启，dba需定期的对回收站进行清理；闪回表删除功能自行测试；