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