從Oracle10g開(kāi)始，Oracle引入了flashback drop的新特性，這個(gè)新特性，允許你從當(dāng)前數(shù)據(jù)庫(kù)中恢復(fù)一個(gè)被drop了的對(duì)象。在執(zhí)行drop操作時(shí)，現(xiàn)在Oracle不是真正刪除它，而是將該對(duì)象自動(dòng)將放入回收站。對(duì)于一個(gè)對(duì)象的刪除，其實(shí)僅僅就是簡(jiǎn)單的重令名操作。
所謂的回收站，是一個(gè)虛擬的容器，用于存放所有被刪除的對(duì)象。在回收站中，被刪除的對(duì)象將占用創(chuàng)建時(shí)的同樣的空間，你甚至還可以對(duì)已經(jīng)刪除的表查詢，也可以利用flashback功能來(lái)恢復(fù)它， 這個(gè)就是flashback drop功能。
這個(gè)功能雖然可以極大的簡(jiǎn)化誤drop導(dǎo)致的恢復(fù)操作，但是長(zhǎng)時(shí)間的積累可能會(huì)導(dǎo)致大量的空間占用(雖然Oracle具有自己的清理機(jī)制)，很多時(shí)候我們需要手工介入去清理回收站。本文主要介紹清理回收站的幾種方法.
1.大量累計(jì)的空間占用
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.不同用戶在回收站的對(duì)象
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.我們可以指定刪除某些特定對(duì)象
|
SQL> purge table common.T_SERVICE_CODE_INFO;
Table purged.
|
4.指定清除某個(gè)表空間的所有回收站對(duì)象
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身份可以清除所有回收站對(duì)象
|
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> select owner,count(*) from dba_recyclebin group by owner;
no rows selected
|
6.禁用recyclebin
如果我們不希望使用Oracle的recyclebin,可以通過(guò)參數(shù)禁用這個(gè)特性。
在Oracle10gR1中，通過(guò)修改一個(gè)隱含參數(shù):_recyclebin 為False可以禁用這個(gè)特性:
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變成了一個(gè)常規(guī)參數(shù)，可以在session/system級(jí)動(dòng)態(tài)修改:
[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.
|