How to maintain Oracle10g Recyclebin?
作者: |
【:转载时请务必以超链接形式标明文章和作者信息及】
链接:
从Oracle10g开始,Oracle引入了,这个新特性,允许你从当前数据库中恢复一个被drop了的对象。在执行drop操作时,现在Oracle不是真正删除它,而是将该对象自动将放入回收站。对于一个对象的删除,其实仅仅就是简单的重令名操作。
所谓的回收站,是一个虚拟的容器,用于存放所有被删除的对象。在回收站中,被删除的对象将占用创建时的同样的空间,你甚至还可以对已经删除的表查询,也可以利用flashback功能来恢复它, 这个就是flashback drop功能。
这个功能虽然可以极大的简化误drop导致的恢复操作,但是长时间的积累可能会导致大量的空间占用(虽然Oracle具有自己的清理机制),很多时候我们需要手工介入去清理回收站。本文主要介绍清理回收站的几种方法.
1.大量累计的空间占用
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 Connected as SYS SQL> col owner for a12 SQL> select owner,object_name,CREATETIME,DROPTIME from dba_recyclebin 2 order by droptime 3 /
OWNER OBJECT_NAME CREATETIME DROPTIME ------------ ------------------------------ ------------------- ------------------- COMMON BIN$AHsQ+pi+Kb/gRAADumkBdQ==$0 2005-08-29:16:42:19 2005-09-11:15:36:17 COMMON BIN$AHsQ+pi9Kb/gRAADumkBdQ==$0 2005-08-29:16:42:19 2005-09-11:15:36:17 PDA BIN$AdEb4zqqUcTgRAADumkBdQ==$0 2005-09-05:10:31:01 2005-09-28:15:40:39 ...... BJLAIS_RUN BIN$BtkGRT0dSwfgRAADumkBdQ==$0 2005-11-30:10:54:07 2005-12-01:16:13:17 BJLAIS_RUN BIN$BtkGRT0cSwfgRAADumkBdQ==$0 2005-11-30:10:54:07 2005-12-01:16:13:17 750 rows selected SQL> |
2.不同用户在回收站的对象
SQL> select owner,count(*) from dba_recyclebin group by owner; OWNER COUNT(*) -------------------- ---------- BJLAIS_RUN 44 COMMON 8 MMSBLOG 618 MMSHAWA_RUN 2 PDA 8 RING_RUN 70 6 rows selected. |
3.我们可以指定删除某些特定对象
SQL> purge table common.T_SERVICE_CODE_INFO;
Table purged. |
4.指定清除某个表空间的所有回收站对象
SQL> purge tablespace common; Tablespace purged. SQL> select owner,count(*) from dba_recyclebin group by owner; OWNER COUNT(*) -------------------- ---------- BJLAIS_RUN 44 MMSBLOG 618 MMSHAWA_RUN 2 PDA 8 RING_RUN 70 |
5.以SYSDBA身份可以清除所有回收站对象
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> select owner,count(*) from dba_recyclebin group by owner;
no rows selected
|
6.禁用recyclebin
如果我们不希望使用Oracle的recyclebin,可以通过参数禁用这个特性。
在Oracle10gR1中,通过修改一个隐含参数:_recyclebin 为False可以禁用这个特性:
SQL> set linesize 132 SQL> column name format a30 SQL> column value format a25 SQL> select 2 x.ksppinm name, y.ksppstvl value, 3 y.ksppstdf isdefault, 4 5 decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, 6 decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj 7 from 8 sys.x$ksppi x, 9 sys.x$ksppcv y 10 where 11 x.inst_id = userenv('Instance') and 12 y.inst_id = userenv('Instance') and 13 x.indx = y.indx and 14 x.ksppinm like '%&par%' 15 order by 16 translate(x.ksppinm, ' _', ' ') 17 / Enter value for par: recyclebin old 14: x.ksppinm like '%&par%' new 14: x.ksppinm like '%recyclebin%' NAME VALUE ISDEFAULT ISMOD ISADJ ------------------------------ ------------------------- --------- ---------- ----- _recyclebin TRUE TRUE FALSE FALSE 1 row selected.
|
在Oracle10gR2中,recyclebin变成了一个常规参数,可以在session/system级动态修改:
[oracle@danaly ~]$ sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 12 15:34:56 2005 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SQL> show parameter recyclebin NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on SQL> alter session set recyclebin=off; Session altered. SQL> alter session set recyclebin=on 2 / Session altered. SQL> alter system set recyclebin=off; System altered. SQL> alter system set recyclebin=on; System altered. |
阅读(4038) | 评论(1) | 转发(0) |