Segment in recyclebin? Is it free?

考过10g ocp的朋友大概都看到过这样的问题，回收站中的对象所占空间是否算作free space？ 纸上得来终觉浅，我们实地考察一下:

SQL> set long 99999999;
/*DBA_FREE_SPACE视图列出了数据库中所有表空间上空闲的区间,利用该视图我们可以计算表空间使用率等
注意该视图不会列出本地管理模式中offline的数据文件(或表空间)上的相关区间信息*/

SQL> select text from dba_views where view_name='DBA_FREE_SPACE';

TEXT
--------------------------------------------------------------------------------
select ts.name, fi.file#, f.block#,
       f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select /*+ ordered use_nl(u) use_nl(fi) */
       ts.name, fi.file#, u.ktfbuebno,
       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
  and rb.ts# = fi.ts#
  and u.ktfbuefno = fi.relfile#
  and u.ktfbuesegtsn = rb.ts#
  and u.ktfbuesegfno = rb.file#
  and u.ktfbuesegbno = rb.block#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select ts.name, fi.file#, u.block#,
       u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
  and u.ts# = fi.ts#
  and u.segfile# = fi.relfile#
  and u.ts# = rb.ts#
  and u.segfile# = rb.file#
  and u.segblock# = rb.block#
  and ts.bitmapped = 0

/*可以看到后2个子查询链接中存在recyclebin$基表*/

SQL> show user;
User is "system"

SQL> purge recyclebin;

Done

SQL> create table YOUYUS tablespace users as select * from dba_objects;

Table created

SQL> select sum(bytes) from dba_free_space where tablespace_name='USERS';

SUM(BYTES)
----------
    851968

SQL> drop table YOUYUS;

Table dropped

SQL> col ORIGINAL_NAME   for a10;
SQL> col ts_name for a10;
SQL> select original_name,operation,type,ts_name,space from dba_recyclebin;

ORIGINAL_N OPERATION TYPE                      TS_NAME         SPACE
---------- --------- ------------------------- ---------- ----------
YOUYUS     DROP      TABLE                     USERS            1152
/* 这里的SPACE单位是standard block size,1152 * 8k=9216k */

SQL> select sum(bytes)  from dba_free_space where tablespace_name='USERS';

SUM(BYTES)
----------
  10289152
/* 可以看到YOUYUS表被回收后，USERS表空间上的FREE EXTENT空间也随之增长了;10289152-851968=9216k 与YOUYUS表的大小吻合*/

col name for a10;
/*通过以下查询可以发现数据库中本地管理模式表空间上已被回收对象可以被覆盖重用的区间信息*/
select /*+ ordered use_nl(u) use_nl(fi) */
       ts.name, fi.file#, u.ktfbuebno,
       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
  and rb.ts# = fi.ts#
  and u.ktfbuefno = fi.relfile#
  and u.ktfbuesegtsn = rb.ts#
  and u.ktfbuesegfno = rb.file#
  and u.ktfbuesegbno = rb.block#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0;
NAME            FILE#  KTFBUEBNO U.KTFBUEBLKS*TS.BLOCKSIZE KTFBUEBLKS  KTFBUEFNO
---------- ---------- ---------- ------------------------- ---------- ----------
USERS               4        184                     65536          8          4
USERS               4        192                     65536          8          4
USERS               4        200                     65536          8          4
USERS               4        208                     65536          8          4
USERS               4        216                     65536          8          4
USERS               4        224                     65536          8          4
USERS               4        232                     65536          8          4

So We can reuse segment space which resided in recyclebin!
That' great!