考过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!
Leave a Reply