rac环境下1个节点关闭回收站1个节点打开回收站功能

今天在巡检一个库的时候，发现这个库回收站内有大量的表和索引，而且发现回收站参数设置也很奇怪，1号节点关闭了回收站功能、2号节点打开了回收站功能，之前的文章中测试了单实例情况先删除了表以及索引对象后关闭回收站可能引起ORA-01652报错（关闭回收站后遭遇ora-01652）的情况，那如果RAC环境中一个节点关闭了回收站功能，一个节点打开了回收站功能会出现什么情况喃？下面通过实验来说明

21:32:21 SYS@hcndb1>select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMPTS1
USERS
UNDOTBS2
6 rows selected.
21:32:30 SYS@hcndb1>
21:32:44 SYS@hcndb1>
21:32:44 SYS@hcndb1>
21:32:45 SYS@hcndb1>
21:33:31 SYS@hcndb1>
21:33:32 SYS@hcndb1>
21:33:32 SYS@hcndb1>select file_name from dba_data_files
21:33:42   2  /
FILE_NAME
--------------------------------------------------------------------------------
+DATADG/hcndb/datafile/system.343.836478025
+DATADG/hcndb/datafile/undotbs1.342.836478029
+DATADG/hcndb/datafile/sysaux.341.836478029
+DATADG/hcndb/datafile/users.339.836478029
+DATADG/hcndb/datafile/undotbs2.338.836479735
21:33:43 SYS@hcndb1>
21:34:12 SYS@hcndb1>
21:34:12 SYS@hcndb1>
21:34:12 SYS@hcndb1>select username from dba_users;
USERNAME
------------------------------
OUTLN
SYS
SYSTEM
DBSNMP
TSMSYS
DIP
ORACLE_OCM
7 rows selected.
21:34:25 SYS@hcndb1>
21:34:29 SYS@hcndb1>
21:34:29 SYS@hcndb1>create user hcn identified by oracle;
User created.
21:34:43 SYS@hcndb1>grant resource,create session to hcn;
Grant succeeded.
21:36:56 SYS@hcndb1>
21:37:34 SYS@hcndb1>
21:37:34 SYS@hcndb1>create tablespace test_tbs datafile '+datadg' size 10m autoextend off;
Tablespace created.
21:37:56 SYS@hcndb1>
21:48:32 SYS@hcndb1>
21:48:32 SYS@hcndb1>create table hcn.test tablespace test_tbs as select * from dba_objects;
Table created.
21:48:33 SYS@hcndb1>
21:50:35 SYS@hcndb1>
21:50:36 SYS@hcndb1>select segment_name,bytes/1024/1024 from dba_segments where segment_name='TEST' and owner='HCN';
SEGMENT_NAME                                                                      BYTES/1024/1024
--------------------------------------------------------------------------------- ---------------
TEST                                                                                            2
21:50:36 SYS@hcndb1>
21:50:37 SYS@hcndb1>
21:51:26 SYS@hcndb1>
21:51:26 SYS@hcndb1>insert into hcn.test select * from dba_objects;
9563 rows created.
21:51:27 SYS@hcndb1>insert into hcn.test select * from dba_objects;
9563 rows created.
21:51:28 SYS@hcndb1>insert into hcn.test select * from dba_objects;
9563 rows created.
21:51:43 SYS@hcndb1>insert into hcn.test select * from dba_objects;
9563 rows created.
21:51:47 SYS@hcndb1>insert into hcn.test select * from dba_objects;
9563 rows created.
21:51:48 SYS@hcndb1>insert into hcn.test select * from dba_objects;
9563 rows created.
21:51:52 SYS@hcndb1>insert into hcn.test select * from dba_objects;
9563 rows created.
21:51:53 SYS@hcndb1>commit;
Commit complete.
21:51:56 SYS@hcndb1>select segment_name,bytes/1024/1024 from dba_segments where segment_name='TEST' and owner='HCN';
SEGMENT_NAME                                                                      BYTES/1024/1024
--------------------------------------------------------------------------------- ---------------
TEST                                                                                            8
21:51:59 SYS@hcndb1>
21:52:00 SYS@hcndb1>
21:52:51 SYS@hcndb1>
21:52:51 SYS@hcndb1>show parameter recyclebin
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
21:52:58 SYS@hcndb1>
21:52:58 SYS@hcndb1>
---节点2：
21:53:13 SYS@hcndb2>show parameter recyclebin
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
21:53:22 SYS@hcndb2>
21:53:22 SYS@hcndb2>
---删除表test，进入回收站
21:54:20 SYS@hcndb1>
21:54:20 SYS@hcndb1>select * from dba_recyclebin;
no rows selected
21:54:27 SYS@hcndb1>
21:54:28 SYS@hcndb1>
21:54:28 SYS@hcndb1>drop table hcn.test;
Table dropped.
21:54:35 SYS@hcndb1>select * from dba_recyclebin;
OWNER                          OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        CREATETIME
------------------------------ ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------
DROPTIME               DROPSCN PARTITION_NAME                   CAN CAN    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------- ---------- -------------------------------- --- --- ---------- ----------- ------------ ----------
HCN                            BIN$9PbCk1lB047gQKjAZWQWFg==$0 TEST                             DROP      TABLE                     TEST_TBS                       2014-03-19:21:48:33
2014-03-19:21:54:34     257900                                  YES YES       9928        9928         9928       1024
21:54:38 SYS@hcndb1>
21:54:39 SYS@hcndb1>
--- 关闭节点1的回收站功能
21:55:41 SYS@hcndb1>
21:55:41 SYS@hcndb1>alter system set recyclebin=off sid='hcndb1';
System altered.
21:55:58 SYS@hcndb1>
21:55:58 SYS@hcndb1>show parameter recyclebin 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF
21:56:13 SYS@hcndb1>
21:56:13 SYS@hcndb1>
21:53:13 SYS@hcndb2>show parameter recyclebin
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
21:53:22 SYS@hcndb2>
21:53:22 SYS@hcndb2>
21:56:17 SYS@hcndb2>
21:56:18 SYS@hcndb2>show parameter recyclebin 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
21:56:19 SYS@hcndb2>
21:56:19 SYS@hcndb2>
--- 2号节点的回收站功能依然开启
在HCN用户创建一个测试表：
21:57:07 SYS@hcndb1>select username,default_tablespace from dba_users;
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
OUTLN                          SYSTEM
SYS                            SYSTEM
SYSTEM                         SYSTEM
HCN                            USERS
DBSNMP                         SYSAUX
TSMSYS                         USERS
DIP                            USERS
ORACLE_OCM                     USERS
8 rows selected.
21:57:25 SYS@hcndb1>
21:57:29 SYS@hcndb1>
21:57:29 SYS@hcndb1>create table hcn.objtb as select * from dba_objects;
Table created.
21:57:50 SYS@hcndb1>select owner,segment_name,tablespace_name,bytes/1024/1024 from dba_segments where owner='HCN' and segment_name='OBJTB';
OWNER                          SEGMENT_NAME                                                                      TABLESPACE_NAME                BYTES/1024/1024
------------------------------ --------------------------------------------------------------------------------- ------------------------------ ---------------
HCN                            OBJTB                                                                             USERS                                    .9375
21:58:30 SYS@hcndb1>
21:58:31 SYS@hcndb1>insert into hcn.objtb select * from dba_objects;
9563 rows created.
21:58:47 SYS@hcndb1>/
9563 rows created.
21:58:48 SYS@hcndb1>/
9563 rows created.
21:58:49 SYS@hcndb1>/
9563 rows created.
21:58:50 SYS@hcndb1>/
9563 rows created.
21:58:51 SYS@hcndb1>/
9563 rows created.
21:58:51 SYS@hcndb1>/
9563 rows created.
21:58:52 SYS@hcndb1>/
9563 rows created.
21:58:53 SYS@hcndb1>commit;
Commit complete.
21:58:55 SYS@hcndb1>select owner,segment_name,tablespace_name,bytes/1024/1024 from dba_segments where owner='HCN' and segment_name='OBJTB';
OWNER                          SEGMENT_NAME                                                                      TABLESPACE_NAME                BYTES/1024/1024
------------------------------ --------------------------------------------------------------------------------- ------------------------------ ---------------
HCN                            OBJTB                                                                             USERS                                        9
21:58:59 SYS@hcndb1>
---该测试表的大小为9M
---在节点1执行下面语句
21:59:44 SYS@hcndb1>
21:59:44 SYS@hcndb1>create table hcn.test1 tablespace test_tbs as select * from hcn.objtb;
create table hcn.test1 tablespace test_tbs as select * from hcn.objtb
                                                                *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEST_TBS
22:00:41 SYS@hcndb1>
---在节点2再次执行相同语句
22:01:24 SYS@hcndb2>
22:01:25 SYS@hcndb2>create table hcn.test1 tablespace test_tbs as select * from hcn.objtb;
Table created.
22:01:27 SYS@hcndb2>
22:01:28 SYS@hcndb2>
22:00:41 SYS@hcndb1>select * from dba_recyclebin;
no rows selected
22:01:47 SYS@hcndb1>
22:01:47 SYS@hcndb1>

结论：
在RAC环境中如果一个节点的回收站功能关闭后，但是回收站内的空间未释放，
如果继续在这个节点创建表等对象，需要空间时，并不会回收回收站内的空间，
如果是在另外一个打开了回收站功能的节点执行该操作的话，会正常回收回收站内的空间