1
文档上是这样描述的
The recycle bin is a logical container for all dropped tables and their dependent objects.我在想RECYCLEBIN是存在什么地方的,应该是表空间中对应一个RECYCLEBIN吧?
2
The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments (objects) is in a dictionary-managed tablespace, then these objects are (not)protected by the recycle bin.这段话中的那个红字NOT是我自己加进去的,原文中没有。
呵呵,根据上下文,我大胆的猜测文档里少打个not,不知道对不对。希望了解的人指点一下。
文档上是这样描述的
The recycle bin is a logical container for all dropped tables and their dependent objects.我在想RECYCLEBIN是存在什么地方的,应该是表空间中对应一个RECYCLEBIN吧?
2
The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments (objects) is in a dictionary-managed tablespace, then these objects are (not)protected by the recycle bin.这段话中的那个红字NOT是我自己加进去的,原文中没有。
呵呵,根据上下文,我大胆的猜测文档里少打个not,不知道对不对。希望了解的人指点一下。
关于recycle bin
http://www.dbanotes.net/Oracle/Oracle-10G-RecycleBin.htm2.
后面一点我赞同你的意见,既
"如果segment在字典管理的表空间中时也无法使用recycle bin功能",
等待高手确认
ECYCLEBIN%';OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
RECYCLEBIN$ TABLE
RECYCLEBIN$_OBJ INDEX
RECYCLEBIN$_TS INDEX
RECYCLEBIN$_OWNER INDEX
USER_RECYCLEBIN VIEW
USER_RECYCLEBIN SYNONYM
RECYCLEBIN SYNONYM
DBA_RECYCLEBIN VIEW
DBA_RECYCLEBIN SYNONYM
SQL> show user;
USER is "SYS"
SQL> select object_name, object_type from all_objects where object_name like '%R
2 ECYCLEBIN%'; no rows selected
SQL> conn / as sysdba
Connected.
SQL> select object_name, object_type from all_objects where object_name like '%R 2 ECYCLEBIN%';no rows selectedSQL> select object_name, object_type from dba_objects where object_name like '%R 2 ECYCLEBIN%';no rows selectedSQL>
不过好像并没有解决我的问题
我想问的是每个数据库中是不是只有一个RECYCLEBIN,抑或是多个?
以下是REFERENCE里的一句话
DBA_RECYCLEBINDBA_RECYCLEBIN displays information about all recycle bins in the database.
这么看来好像有多个哦?那是一个表空间对应一个还是每个SCHEMA对应一个?
每个schema 对应不同的 recyclebinSQL> conn / as sysdba
Connected.
SQL> grant select on hr.employees to cc;Grant succeeded.SQL> conn cc/cc
Connected.
SQL> select username,default_tablespace from user_users;USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
CC USERS SQL> create table test2 as select * from hr.employees;Table created.SQL> show recyclebin;
SQL> drop table test2;Table dropped.SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST2 BIN$dA+pn9uEGMrgRAAfKQ3wlg==$0 TABLE 2009-09-21:13:22:06SQL> conn cx/cx
Connected.
SQL> select username,default_tablespace from user_users;USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
CX USERSSQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$dAxr3kFEXJvgRAAfKQ3wlg==$0 TABLE 2009-09-21:09:30:05
SQL>
Connected.
SQL> create tablespace test2;Tablespace created.SQL> alter user ccc default tablespace test2;User altered.SQL> conn ccc/ccc
Connected.
SQL> select username,default_tablespace from user_users;USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
CCC TEST2
SQL> create table test3 as select * from hr.employees;Table created. SQL> show recyclebin;
SQL> drop table test3;Table dropped.SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST3 BIN$dBAG9x8ERNfgRAAfKQ3wlg==$0 TABLE 2009-09-21:13:48:12
SQL> conn ccc/cccSQL> alter user ccc default tablespace users;User altered.SQL> conn ccc/ccc
Connected.
SQL> select username,default_tablespace from user_users;USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
CCC USERSSQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST3 BIN$dBAG9x8ERNfgRAAfKQ3wlg==$0 TABLE 2009-09-21:13:48:12
SQL> conn / as sysdba
Connected.
SQL> drop tablespace test2;Tablespace dropped.SQL> conn ccc/ccc
Connected.
SQL> show recyclebin;
SQL>
recyclebin应该是10g的新特性。你的是低于10g的版本。
recyclebin$是系统表,你这里的测试是干掉了tablespace里,你这里连tablespace都drop掉了,所以oracle应该在你drop掉表空间的过程中,用递归sql处理了recyclebin的记录叻。这些对于我们的操作是透明的,所以你就有这样的误解了。
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 21 19:03:26 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn / as sysdba
Connected.
SQL> select * from v$version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - ProductionSQL>2
个人感觉哦,这个RECYCLEBIN相当于一个逻辑结构,是对应于每个SCHEMA的。
就像上面的那位朋友做的实验那样
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST3 BIN$dBAG9x8ERNfgRAAfKQ3wlg==$0 TABLE 2009-09-21:13:48:12
SQL> conn ccc/cccSQL> alter user ccc default tablespace users;User altered.SQL> conn ccc/ccc
Connected.
SQL> select username,default_tablespace from user_users;USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
CCC USERSSQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST3 BIN$dBAG9x8ERNfgRAAfKQ3wlg==$0 TABLE 2009-09-21:13:48:12用户的默认表空间更改以后,RECYCLEBIN里的内容并没有改变。
我把用户ccc的默认表空间从test2改成users之后,recyclebin 仍然存在,
我为了验证这个时候
用户原来的recycle bin 还保存在表空间test2中,
在那段带码下面,加上了SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST3 BIN$dBAG9x8ERNfgRAAfKQ3wlg==$0 TABLE 2009-09-21:13:48:12
SQL> conn / as sysdba
Connected.
SQL> drop tablespace test2;Tablespace dropped.SQL> conn ccc/ccc
Connected.
SQL> show recyclebin;
SQL>
如果recycle bin是在 system表空间里的,
那么我此时删除这个表空间test2,
recyclebin里应该有数据的,但是实际情况是,
这个时候recyclebin里面已经没有数据了,
可见recycle bin数据并没有保存在system表空间,
而是用户默认的表空间
是移动到USERS中还是继续保留在ccc原有的默认表空间TEST2中?分析一下1
移动到USERS中好像不太可能,因为如果真的移动到了USERS中,那么当你DROP TABLESPACE TEST2了之后再次SHOW RECYCLEBIN应该是有结果的,而不应该是空的。2
保留在TEST2中倒不是没有可能,不过这样的话就和你的大前提“RECYCLEBIN保存在用户默认临时表空间中”相违背了。因为如果事实是像你说的那样的话,当你更改ccc默认表空间的时候,系统肯定会想办法把RECYCLEBIN移动到ccc用户当前默认表空间中,也就是USERS中,可以上第一点论证恰好说明了这是不可能的。3
存在于SYSTEM表空间中。
感觉这句有逻辑问题:
1
移动到USERS中好像不太可能,因为如果真的移动到了USERS中,那么当你DROP TABLESPACE TEST2了之后再次SHOW RECYCLEBIN应该是有结果的,而不应该是空的
好比是你家里有一个垃圾筒,你搬家了,
但是垃圾筒里的垃圾还会被你一起拿到新家吗?我的意思是,recycle bin最开始当然是建立在表空间test2中的,(因为test2 表空间是 ccc的默认表空间)
当你改动用户的 默认表空间到users表空间了,(相当于ccc搬家了)
这个时候用户会在 默认表空间users里建立一个新的 recyclebin(新家当然要有垃圾桶了)
但是这个垃圾筒里面是没有 原来的数据的.
1. recyclebin是个synonym,是user_recyclebin的同义词,user_recyclebin是个view。我现在没有10g的环境,这个view我推测的话,是用recyclebin$做查询,而这个recycle$,如我早上说查询的是sys里的对象,表空间是system,可以看看早上我回的帖所以这点就可以说明这里的recyclebin不是在user的表空间里的,2. 至于为什么drop掉了tablespace,而recyclebin里的记录也没有了,这个应该是在drop tablespace命令的时候,oracle会执行一些递归sql,大家回想一下,我们经常会看到一些sql执行的时候,会报递归sql错误。就是oracle的一些命令,不是单纯的一个命令,会去执行一些自己的sql,比如,当我们drop tablespace的时候,oracle肯定会去做一些动作tablespace的一些相关表都会去做更新,不过这些是透明的,我们看不到而已。所以这里delete这个recyclebin$里的一些和这个tablespace的相关的sql也是在这个命令里执行的。也就产生了你这样的误解。
SQL> create tablespace test1;Tablespace created.SQL> create user test1 identified by test1
2 default tablespace test1
3 quota unlimited on test1;User created.SQL> grant dba to test1;Grant succeeded.SQL> conn test1/test1
Connected.
SQL> create table test(id number)
2 ;Table created.SQL> show recyclebin
SQL> drop table test;Table dropped.SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -----------TEST BIN$6lw8DPnYRF+gUsa7oMr5kg==$0 TABLE 2009-09-21:SQL> create table test2(id number);Table created.SQL> select * from test2;no rows selectedSQL> create or replace trigger test3
2 after update or delete on recyclebin$
3 for each row
4 begin
5 insert into test2 values(1);
6 end test3;
7 /
after update or delete on recyclebin$
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> show recyclebin$
SQL> desc recyclebin$
ERROR:
ORA-04043: object recyclebin$ does not exist
SQL> show user
USER is "TEST1"
SQL> conn / as sysdba
Connected.
SQL> create or replace trigger test3
2 after update or delete on recyclebin$
3 for each row
4 begin
5 insert into test2 values(1);
6 end test3;
7 /
create or replace trigger test3
*
ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYS
SQL>