重庆思庄Oracle、Redhat认证学习论坛
标题: 数据库回收站 [打印本页]
作者: denglj 时间: 2022-9-8 18:02
标题: 数据库回收站
文档课题：数据库回收站.
1、概念数据库回收站从oracle10g引入.从原理上来说，回收站是一个数据字典表，放置用户删掉的数据库对象.被存储在回收站中对象，仍然会占用空间，除非用户手工进行purge或因储存空间不够而被清掉.如果一个表被删除，那么与该表相关联的对象，如索引、约束和其他依赖对象都会加bin$$前缀.
1.1、启动状态查回收站启动情况
SQL> show parameter recyclebin;
NAME TYPE VALUE
---------------------------------------------------------- ------------------------------
recyclebin string on
1.2、修改状态SQL> alter system set recyclebin=onscope=spfile;
SQL> alter session set recyclebin=onscope=spfile;
SQL> alter system set recyclebin=offscope=spfile;
SQL> alter session set recyclebin=offscope=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 [renameto];
将回收站里的表恢复为原名或指定新名，表中数据不会丢失.
若要彻底删除表，则使用语句droptable tablename purge;
1.5、清理回收站清除回收站里的信息
清除指定表：purgetable tablename;
清除当前用户的回收站：purgerecyclebin;
清除所有用户的回收站：purgedba_recyclebin;
数据库升级前回收站处理：
SQL> select sum(bytes)/1024/1024 gb fromdba_segments where segment_name in (select object_name from dba_recyclebin);
GB
----------
SQL> purge dba_recyclebin;
DBA 回收站已清空.
2、实际操作数据库版本：oracle19.3
2.1、异名对象SQL> create table t1 as select * fromemployees;
表已创建.
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,typefrom 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,typefrom 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,typefrom user_recyclebin;
未选定行
2.2、同名对象SQL> drop table t1;
表已删除.
SQL> create table t1 as select * fromDEPARTMENTS;
表已创建.
SQL> drop table t1;
表已删除.
SQL> create table t1 as select * fromemployees;
表已创建.
SQL> drop table t1;
表已删除.
SQL> select original_name,droptime fromuser_recyclebin where original_name='T1' order by droptime;
ORIGINAL_NAME DROPTIME
----------------------------------------------------------
T1 2022-08-29:10:44:14
T1 2022-08-29:10:44:48
T1 2022-08-29:10:45:33
SQL> purge table t1;
表已清除.
SQL> select original_name,droptime fromuser_recyclebin where original_name='T1' order by droptime;
ORIGINAL_NAME DROPTIME
----------------------------------------------------------
T1 2022-08-29:10:44:48
T1 2022-08-29:10:45:33
SQL> selectobject_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中存在同名对象，purgetable table_name时，最先被删除的对象先从recyclebin中释放.
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |