欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-01 19:12:33
关于recyclebin使用中的一些问题 涉及版本: oracle database - enterprise edition - version 11.2.0.1 to 12.2.0.1 [rel...

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