数据库回收站

文档课题：数据库回收站.
1、概念
数据库回收站从oracle10g引入.从原理上来说，回收站是一个数据字典表，放置用户删掉的数据库对象.被存储在回收站中对象，仍然会占用空间，除非用户手工进行purge或因储存空间不够而被清掉.如果一个表被删除，那么与该表相关联的对象，如索引、约束和其他依赖对象都会加bin$$前缀.
1.1、启动状态
查回收站启动情况
SQL> show parameter recyclebin;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
recyclebin                           string                 on
1.2、修改状态
SQL> alter system set recyclebin=on scope=spfile;
SQL> alter session set recyclebin=on scope=spfile;
SQL> alter system set recyclebin=off scope=spfile;
SQL> alter session set recyclebin=off scope=spfile;
1.3、直接删除
以下几种drop不会将相关对象放进recyclebin.
drop tablespace：会将recyclebin中所有属于该tablespace的对象清除
drop user：会将recyclebin中所有属于该用户的对象清除
drop cluster：会将recyclebin中所有属于该cluster的成员对象清除
drop type：会将recyclebin中所有依赖该type的对象清除
1.4、闪回表
查user_recyclebin获取被删除的表信息，然后使用语句
flashback table tablename to before drop [rename to];
将回收站里的表恢复为原名或指定新名，表中数据不会丢失.
若要彻底删除表，则使用语句drop table tablename purge;
1.5、清理回收站
清除回收站里的信息
清除指定表：purge table tablename;
清除当前用户的回收站：purge recyclebin;
清除所有用户的回收站：purge dba_recyclebin;
数据库升级前回收站处理：
SQL> select sum(bytes)/1024/1024 gb from dba_segments where segment_name in (select object_name from dba_recyclebin);

        GB
----------

SQL> purge dba_recyclebin;

DBA 回收站已清空.
2、实际操作
数据库版本：oracle 19.3
2.1、异名对象
SQL> create table t1 as select * from employees;

表已创建.

SQL> create table t2 as select * from t1;

表已创建.
SQL> select * from tab;

TNAME                TABTYPE                     CLUSTERID
-------------------- -------------------------- ----------
T1                   TABLE
T2                   TABLE

已选择 2 行.
SQL> drop table t2;

表已删除.

SQL> select object_name,original_name,operation,type from user_recyclebin;
OBJECT_NAME                    ORIGINAL_NAME        OPERATION            TYPE
------------------------------ -------------------- -------------------- ----------
BIN$zPmMbTcsRMSn4EdPl5O29Q==$0 T2                   DROP                 TABLE

SQL> flashback table t2 to before drop;

闪回完成.

SQL> select * from tab;

TNAME                TABTYPE                     CLUSTERID
-------------------- -------------------------- ----------
T1                   TABLE
T2                   TABLE

已选择 2 行.
SQL> select object_name,original_name,operation,type from user_recyclebin;

未选定行
SQL> drop table t2 purge;  --加purge

表已删除.

SQL> select * from tab;

TNAME                TABTYPE                     CLUSTERID
-------------------- -------------------------- ----------
T1                   TABLE

已选择 1 行.
SQL> select object_name,original_name,operation,type from user_recyclebin;
未选定行
2.2、同名对象
SQL> drop table t1;

表已删除.

SQL> create table t1 as select * from DEPARTMENTS;

表已创建.

SQL> drop table t1;

表已删除.

SQL> create table t1 as select * from employees;

表已创建.

SQL> drop table t1;

表已删除.

SQL> select original_name,droptime from user_recyclebin where original_name='T1' order by droptime;

ORIGINAL_NAME        DROPTIME
-------------------- --------------------------------------
T1                   2022-04-23:10:44:14
T1                   2022-04-23:10:44:48
T1                   2022-04-23:10:45:33
SQL> purge table t1;

表已清除.

SQL> select original_name,droptime from user_recyclebin where original_name='T1' order by droptime;

ORIGINAL_NAME        DROPTIME
-------------------- --------------------------------------
T1                   2022-04-23:10:44:48
T1                   2022-04-23:10:45:33
SQL> select object_name,original_name,operation,type from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME        OPERATION            TYPE
------------------------------ -------------------- -------------------- ----------
BIN$ZQ9GjdgUSU6vwafF4mMFPw==$0 T1                   DROP                 TABLE
BIN$+XXS+h6aR8GvUZkwFSBsSA==$0 T1                   DROP                 TABLE
结论：当recyclebin中存在同名对象，purge table table_name时，最先被删除的对象先从recyclebin中释放.