一. 问题描述

在测试flashback 的功能时,发现drop表时,回收站功能没有能够启用.

二. 问题分析

数据库的flashback和recyclebin功能是开放的.
sql>select flashback_on from v$database;
FLASHBACK_ON
------------------------------------------------------
YES

SQL>show parameter recyclebin
NAME TYPE VALUE
---------------------- --------------------------------- --------
recyclebin string ON

SQL>create table t(x number(10));

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

X
----------
1

SQL> drop table t;
Table dropped.

####检查回收站里面,有没有刚才删除的表,结果显示没有任何记录.
SQL> show recyclebin
SQL> select * from user_recyclebin;
no rows selected

检查用户缺省表空间,原来hr用户的缺省表空间为system,hr.t表建立在system表空间上,所以删除该表时,因为它存在于system表空上

,不放入回收站.
SQL> select username ,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
---------------------------------------- --------------------
OLTP1 SYSTEM
OUTLN SYSTEM
HR SYSTEM
SYS SYSTEM
SYSTEM SYSTEM
TSMSYS SYSTEM
DIP SYSTEM
DBSNMP SYSAUX
WMSYS SYSAUX

三. 问题解决
修改用户的缺省表空间(非system表空间)
SQL>alter user hr default tablespace users;
User altered.

再次测试删除表的操作.
SQL> create table t (id number(10));
Table created.
SQL> insert into t values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table t;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$vkI6NtLWip7gQKjAgdAVoA==$0 TABLE 2012-04-22:02:34:25