oracle drop恢复oracle中误删除drop掉的表的操作教程

关于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进行手动清理。