Oracle 10g recyclebin 使用限制

根据官方文档，我们使用recyclebin有以下限制：

引用

A few limitations are associated with the recycle bin:
·Only non-SYSTEM locally managed tablespaces can have a recycle bin. However, dependent objects in a dictionary-managed tablespace are protected if the dropped object is in a locally managed tablespace.
·A table’s dependent objects are saved in the recycle bin when the table is dropped, except for bitmap join indexes, referential integrity constraints (foreign key constraints), and materialized view logs.
·Indexes are protected only if the table is dropped first; explicitly dropping an index does notplace the index into the recycle bin.

当然使用recyclebin需要将参数RECYCLEBIN设置为on，该参数可以在系统级别或者会话级别打开。其官方解释为：

引用

RECYCLEBIN is used to control whether the Flashback Drop capability is turned on or off. If the parameter is set to OFF, then dropped tables do not go into the recycle bin. If this parameter is set to ON, dropped tables go into the recycle bin and can be recovered.

但是今天碰到一件怪事，在系统级别设置为off时，drop table照样在recyclebin存在。再次在会话级别设置为off时，才生效

引用

[ora10g@mcprod ~]$ sqlplus "zhou/zhou"

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Nov 20 11:42:13 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table t as select * from zhou;
SQL> drop table t;
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T                BIN$lXPP0Tic6k/gQBCsyAR3gg==$0 TABLE        2010-11-20:11:42:27
SQL> show parameter rec
buffer_pool_recycle                  string
control_file_record_keep_time        integer     7
db_recovery_file_dest                string      /Tbackup
db_recovery_file_dest_size           big integer 10G
db_recycle_cache_size                big integer 0
ldap_directory_access                string      NONE
recovery_parallelism                 integer     0
recyclebin                           string      OFF
use_indirect_data_buffers            boolean     FALSE
SQL> create table tt as select * from sys.obj$;
SQL> drop table tt;
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T                BIN$lXPP0Tic6k/gQBCsyAR3gg==$0 TABLE        2010-11-20:11:42:27
TT               BIN$lXPP0Tim6k/gQBCsyAR3gg==$0 TABLE        2010-11-20:11:44:47
SQL> drop table zhou;
SQL>  show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T                BIN$lXPP0Tic6k/gQBCsyAR3gg==$0 TABLE        2010-11-20:11:42:27
TT               BIN$lXPP0Tim6k/gQBCsyAR3gg==$0 TABLE        2010-11-20:11:44:47
ZHOU             BIN$lXPP0Tiw6k/gQBCsyAR3gg==$0 TABLE        2010-11-20:11:45:10
SQL> create table ttt as select * from sys.obj$;
SQL> alter session set recyclebin=off;
SQL> drop table ttt;
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T                BIN$lXPP0Tic6k/gQBCsyAR3gg==$0 TABLE        2010-11-20:11:42:27
TT               BIN$lXPP0Tim6k/gQBCsyAR3gg==$0 TABLE        2010-11-20:11:44:47
ZHOU             BIN$lXPP0Tiw6k/gQBCsyAR3gg==$0 TABLE        2010-11-20:11:45:10