Oracle10g 的Recyclebin簡介
從Oracle10g開始,Oracle引入了flashback drop的新特性,這個新特性,允許你從當前資料庫中恢復一個被drop了的物件。在執行drop操作時,現在Oracle不是真正刪除它,而是將該物件自動將放入回收站。對於一個物件的刪除,其實僅僅就是簡單的重令名操作。
所謂的回收站,是一個虛擬的容器,用於存放所有被刪除的物件。在回收站中,被刪除的物件將佔用建立時的同樣的空間,你甚至還可以對已經刪除的表查詢,也可以利用flashback功能來恢復它,這個就是flashback drop功能。
這個功能雖然可以極大的簡化誤drop導致的恢復操作,但是長時間的積累可能會導致大量的空間佔用(雖然Oracle具有自己的清理機制),很多時候我們需要手工介入去清理回收站。本文主要介紹清理回收站的幾種方法.
1.大量累計的空間佔用
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
With the Partitioning, OLAP and Data Mining options
SQL> col owner for a12
SQL> select owner,object_name,CREATETIME,DROPTIME from dba_recyclebin;
SQL> select owner,object_name,CREATETIME,DROPTIME from dba_recyclebin;
OWNER OBJECT_NAME CREATETIME DROPTIME
------------------------------ ------------------------------ ------------------- -------------------
TSS BIN$mHengGfGHfvgQKjAMwBm4g==$0 2010-12-17:17:59:07 2010-12-28:19:21:30
TSS BIN$mHengGfFHfvgQKjAMwBm4g==$0 2010-12-17:17:59:07 2010-12-28:19:21:30
TSS BIN$mHengGfEHfvgQKjAMwBm4g==$0 2010-12-17:17:59:07 2010-12-28:19:21:30
TSS BIN$mHengGfHHfvgQKjAMwBm4g==$0 2010-12-17:17:59:07 2010-12-28:19:21:30
------------------------------ ------------------------------ ------------------- -------------------
TSS BIN$mHengGfGHfvgQKjAMwBm4g==$0 2010-12-17:17:59:07 2010-12-28:19:21:30
TSS BIN$mHengGfFHfvgQKjAMwBm4g==$0 2010-12-17:17:59:07 2010-12-28:19:21:30
TSS BIN$mHengGfEHfvgQKjAMwBm4g==$0 2010-12-17:17:59:07 2010-12-28:19:21:30
TSS BIN$mHengGfHHfvgQKjAMwBm4g==$0 2010-12-17:17:59:07 2010-12-28:19:21:30
......
TSS BIN$miz+VtWDp2ngQKjAMwBRjg==$0 2011-01-01:00:01:13 2011-01-19:15:44:22
TSS BIN$miz+VtWCp2ngQKjAMwBRjg==$0 2011-01-01:00:01:13 2011-01-19:15:44:22
TSS BIN$miz+VtWBp2ngQKjAMwBRjg==$0 2011-01-01:00:01:13 2011-01-19:15:44:22
TSS BIN$miz+VtWDp2ngQKjAMwBRjg==$0 2011-01-01:00:01:13 2011-01-19:15:44:22
TSS BIN$miz+VtWCp2ngQKjAMwBRjg==$0 2011-01-01:00:01:13 2011-01-19:15:44:22
TSS BIN$miz+VtWBp2ngQKjAMwBRjg==$0 2011-01-01:00:01:13 2011-01-19:15:44:22
1838 rows selected
SQL>
2.不同使用者在回收站的物件
SQL> select owner,count(*) from dba_recyclebin group by owner;
OWNER COUNT(*)
------------------------------ ----------
TSS 1799
XB 39
------------------------------ ----------
TSS 1799
XB 39
3.我們可以指定刪除某些特定物件
SQL> purge table TSS.T_CODE_INFO;
Table purged.
4.指定清除某個表空間的所有回收站物件
SQL> purge tablespace TSS;
Tablespace purged.
Tablespace purged.
SQL> select owner,count(*) from dba_recyclebin group by owner;
OWNER COUNT(*)
------------------------------ ----------
XB 39
------------------------------ ----------
XB 39
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 x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf, 7),
1,
'MODIFIED',
4,
'SYSTEM_MOD',
'FALSE') ismod,
decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj
from sys.x$ksppi x, sys.x$ksppcv y
where x.inst_id = userenv('Instance')
and y.inst_id = userenv('Instance')
and x.indx = y.indx
and x.ksppinm like '%&par%'
order by translate(x.ksppinm, ' _', ' '); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
輸入 par 的值: recyclebin
原值 15: and x.ksppinm like '%&par%'
新值 15: and x.ksppinm like '%recyclebin%'
NAME VALUE ISDEFAULT ISMOD ISADJ
------------------------------ ---------------------------------------- --------- ---------- -----
recyclebin on TRUE FALSE FALSE
1 row selected.
------------------------------ ---------------------------------------- --------- ---------- -----
recyclebin on TRUE FALSE FALSE
1 row selected.
在Oracle10gR2中,recyclebin變成了一個常規引數,可以在session/system級動態修改:
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 1月 19 17:16:17 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining 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;
Session altered.
SQL> alter session set recyclebin=on;
Session altered.
SQL> alter system set recyclebin=off;
System altered.
SQL> alter system set recyclebin=on;
System altered.
SQL> alter system set recyclebin=off;
System altered.
SQL> alter system set recyclebin=on;
System altered.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9399028/viewspace-684270/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle10g的recyclebinOracle
- oracle10g recyclebinOracle
- oracle10g recyclebin 詳解Oracle
- How to maintain Oracle10g RecyclebinAIOracle
- Oracle recyclebin介紹Oracle
- Oracle10g的回收站(recyclebin)和自由空間管理Oracle
- Oracle10g的回收站(recyclebin)和自由空間管理(zt)Oracle
- Oracle10g SQL tune adviser簡單介紹OracleSQL
- Linux recyclebinLinux
- Oracle Recyclebin - purgeOracle
- recyclebin in ORACLE 10Oracle
- ListView 中的 RecycleBin 機制View
- recyclebin造成的問題分析
- oracle recyclebin詳解Oracle
- oracle recyclebin和flashbackOracle
- 清除oracle的recyclebin回收站Oracle
- Oracle10g新功能介紹Oracle
- Oracle10g的簡化連線方式Oracle
- Oracle10g RAC的一些服務(CRS),trc及log檔案簡介[final]Oracle
- recyclebin for oracle 10gOracle 10g
- spark 的簡介Spark
- Hudson的簡介
- SVN的簡介
- html的簡介HTML
- css的簡介CSS
- activiti的簡介
- Oracle10g的AWR效能優化工具介紹Oracle優化
- 原始碼解析ListView中的RecycleBin機制原始碼View
- 一、群集的簡介
- QC的使用簡介
- GreenDao的使用簡介
- expdp 的用法簡介
- TKPROF的使用簡介
- Bioperl的簡介 (轉)
- 簡介
- Jira使用簡介 HP ALM使用簡介
- JSON簡介和Ajax簡介--bea這兩篇文章介紹的不錯JSON
- 【RECYCLEBIN】Oracle回收站詳解Oracle