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

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

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