10g recyclebin与用户表空间限额

关于recyclebin

(1) recyclebin是一个逻辑结构.是由数据字典表来实现的.
(2) recyclebin中数据对象的删除是遵循先进先出方式.
(3) 表及其依赖对象进入recyclebin后，它们占用的表空间在dba_free_space中显示为可用空间。
(4) 表及其依赖对象进入recyclebin后，依然会占用用户的表空间限额。当用户限额不足时oracle自动删除recyclebin中的对象。

测试过程：
SQL> create tablespace testtbs datafile '/u01/app/oracle/oradata/oraebao/testtbs01.dbf' size 500M autoextend off
  2  extent management local uniform. size 1M segment space management auto;

Tablespace created.

SQL> create user yujun identified by yujun default tablespace testtbs temporary tablespace temp quota 200M on testtbs;

User created.
SQL> grant dba to yujun;

Grant succeeded.

SQL> conn yujun/yujun;
Connected.
SQL>
SQL> create table tab1 as select * from dba_objects;

Table created.

SQL> insert into tab1 select * from tab1;

53545 rows created.

SQL> /

107090 rows created.

SQL> /

214180 rows created.

SQL> /

428360 rows created.

SQL> commit;

Commit complete.

SQL> select tablespace_name,sum(bytes/1024/1204) as free_M from dba_free_space where tablespace_name='TESTTBS' group by tablespace_name;

TABLESPACE_NAME                    FREE_M
------------------------------ ----------
TESTTBS                        345.302326

SQL> select TABLESPACE_NAME,username,bytes,max_bytes,dropped from dba_ts_quotas where tablespace_name='TESTTBS' and username='YUJUN';

TABLESPACE_NAME                USERNAME                  BYTES  MAX_BYTES DROPPE
------------------------------ -------------------- ---------- ---------- ------
TESTTBS                        YUJUN                  97517568  209715200 NO

SQL> drop table tab1;

Table dropped.

SQL> select tablespace_name,sum(bytes/1024/1204) as free_M from dba_free_space where tablespace_name='TESTTBS' group by tablespace_name;

TABLESPACE_NAME                    FREE_M
------------------------------ ----------
TESTTBS                        424.398671

表删除以后，它占用的空间在dba_free_space中显示为可用空间。

SQL> select TABLESPACE_NAME,username,bytes,max_bytes,dropped from dba_ts_quotas where tablespace_name='TESTTBS' and username='YUJUN';

TABLESPACE_NAME                USERNAME                  BYTES  MAX_BYTES DROPPE
------------------------------ -------------------- ---------- ---------- ------
TESTTBS                        YUJUN                  97517568  209715200 NO

用户表空间限额未变化。

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TAB1             BIN$QbOz3lpVBSLgQAB/AQAYhA==$0 TABLE        2007-12-20:16:48:06

SQL> purge table "BIN$QbOz3lpVBSLgQAB/AQAYhA==$0";

Table purged.

SQL> select tablespace_name,sum(bytes/1024/1204) as free_M from dba_free_space where tablespace_name='TESTTBS' group by tablespace_name;

TABLESPACE_NAME                    FREE_M
------------------------------ ----------
TESTTBS                        424.398671

SQL> select TABLESPACE_NAME,username,bytes,max_bytes,dropped from dba_ts_quotas where tablespace_name='TESTTBS' and username='YUJUN';

TABLESPACE_NAME                USERNAME                  BYTES  MAX_BYTES DROPPE
------------------------------ -------------------- ---------- ---------- ------
TESTTBS                        YUJUN                         0  209715200 NO
将recyclebin中的表purge以后,释放了用户表空间限额。

下面看一下recyclebin对用户表空间限额的影响：
SQL> conn /as sysdba
Connected.
SQL> revoke UNLIMITED TABLESPACE from yujun;

Revoke succeeded.

SQL> create table tab1 as select * from dba_objects;

Table created.

SQL> insert into tab1 select * from tab1;

53544 rows created.

SQL> /

107088 rows created.

SQL> /

214176 rows created.

SQL> /

428352 rows created.

SQL> commit;

Commit complete.

SQL> drop table tab1;    

Table dropped.

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TAB1             BIN$QbhhtOKE7cngQAB/AQAaNw==$0 TABLE        2007-12-20:21:03:00

SQL> select segment_name,segment_type,bytes from dba_segments where segment_name='BIN$QbhhtOKE7cngQAB/AQAaNw==$0';

SEGMENT_NAME                             SEGMENT_TYPE              BYTES
---------------------------------------- -------------------- ----------
BIN$QbhhtOKE7cngQAB/AQAaNw==$0           TABLE                  97517568

SQL> select TABLESPACE_NAME,username,bytes,max_bytes,dropped from dba_ts_quotas where tablespace_name='TESTTBS' and username='YUJUN';

TABLESPACE_NAME      USERNAME                            BYTES  MAX_BYTES DROPPE
-------------------- ------------------------------ ---------- ---------- ------
TESTTBS              YUJUN                            97517568  209715200 NO

SQL> create table tab2 as select * from "BIN$QbhhtOKE7cngQAB/AQAaNw==$0";

Table created.

SQL> select TABLESPACE_NAME,username,bytes,max_bytes,dropped from dba_ts_quotas where tablespace_name='TESTTBS' and username='YUJUN';

TABLESPACE_NAME      USERNAME                            BYTES  MAX_BYTES DROPPE
-------------------- ------------------------------ ---------- ---------- ------
TESTTBS              YUJUN                           196083712  209715200 NO

SQL> insert into tab2 select * from tab2 where rownum < 200001;

200000 rows created.

SQL> commit;

Commit complete.

SQL> select TABLESPACE_NAME,username,bytes,max_bytes,dropped from dba_ts_quotas where tablespace_name='TESTTBS' and username='YUJUN';

TABLESPACE_NAME      USERNAME                            BYTES  MAX_BYTES DROPPE
-------------------- ------------------------------ ---------- ---------- ------
TESTTBS              YUJUN                           120586240  209715200 NO

SQL> show recyclebin
SQL>

可以看到，当用户表空间限额不足时，oracle自动删除了recyclebin中的对象。