关于recyclebin使用中的一些问题
涉及版本：
oracle database – enterprise edition – version 11.2.0.1 to 12.2.0.1 [release 11.2 to 12.2]
症状:
在dba_recyclebin表中存在百万个对象，recyclebin清理速度很慢，似乎永远都在清理：
sql> select count(*) from dba_recyclebin ; count(*) ---------- 2069202
从使用以下语句的执行计划可以知道，执行计划未走index scan，而是进行的full table scan.
delete from recyclebin$ execution plan id operation name rows bytes cost (%cpu) time -- --------- ---- ---- ----- ---------- ---- 0 delete statement 9975 (100) 1 delete recyclebin$ 2 table access full recyclebin$ 1 18 9975 (1) 00:02:00 prior plan was index scan: delete from recyclebin$ where bo=:1 ------------------------------------------------------------------------------------ | id | operation | name | rows | bytes | cost (%cpu)| time | ------------------------------------------------------------------------------------ | 0 | delete statement | | | | 3 (100)| | | 1 | delete | recyclebin$ | | | | | | 2 | index range scan| recyclebin$_bo | 1 | 18 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------
原因：
bo列上未建立索引
sqlplus / as sysdba col column_name format a30 select index_name, column_name, column_position from dba_ind_columns where table_name='recyclebin$';
解决方案：
对bo列建立索引。
1. 在bo列上新建索引:
create index recyclebin$_bo on recyclebin$(bo);
2. 重新收集recyclebin$表及索引信息:
exec dbms_stats.gather_table_stats(ownname=>'sys',tabname=>'recyclebin$', cascade=>true);
3. 执行新的purge命令。
涉及版本：
oracle database – enterprise edition – version 12.1.0.1 and later
症状:
12c bigfile表空间下被删除的表recyclebin无法进行自动清理
问题演示:
1）建立bigfile表空间并进行drop表测试。
建立bigfile表空间
create bigfile tablespace test datafile /home/ora12102/app/ora12102/oradata/ora12102/test.dbf' size 10m;
建表
create table test (col1 char(2000), col2 char(2000)) tablespace test;
为表填充数据
begin
for i in 1..100000 loop
insert into test values ('x','x');
commit;
end loop;
end;
/
删表
sql> drop table test;
在alert log中，我们可以看到表空间在膨胀。
fri jan 05 15:07:05 2018 resize operation completed for file# 6, old size 10240k, new size 81920k fri jan 05 15:07:53 2018 resize operation completed for file# 6, old size 81920k, new size 153600k
2）重新建立一张新表并导入数据。
再次插入数据到表中
begin
for i in 1..100000 loop
insert into test values ('x','x');
commit;
end loop;
end;
/
recyclebin中对象未被自动清理
sql> show recyclebin; original name recyclebin name object type drop time ---------------- ------------------------------- ------------ ------------------- test bin$ygjkq4oxottgu+6qvareeq==$0 table 2018-01-05:15:09:23
alert log中可以看到，不管表空间是否已满，recyclebin并未进行自动清理，而表空间还在膨胀。
fri jan 05 15:10:51 2018 resize operation completed for file# 6, old size 153600k, new size 204800k
原因：
目前问题还在调查中，还未给出fix方案。
this problem is still investigated in bug 23094775 : recyclebin on bigfile tablespace is not purged automatically
解决方案：
需要对recyclebin进行手动清理。