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!

关注刘相兵的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

Comments

  1. admin says:

    Resize Does Not Work Although Query Shows Free Space Available ORA-03297
    Applies to:
    Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.2 – Release: 10.1 to 10.2
    Information in this document applies to any platform.
    Symptoms
    Alter tablespace resize size (BigFile Tablespace) results in ORA-03297 file contains blocks of data beyond requested RESIZE value, even though freespace is available.
    Cause

    Some portion of the file in the region to be trimmed is currently in use by a database segment.

    RESEARCH
    ——–
    SQL> select * from dba_free_space where tablespace_name=’USERS’;

    TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
    ——————– ———- ———- ———- ———- ————
    USERS 4 57 4784128 584 4

    ===> Creating table
    SQL> create table emp1 as select * from emp;

    Table created.

    SQL> select * from dba_free_space where tablespace_name=’USERS’;

    TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
    ————— ———- ———- ———- ———- ————
    USERS 4 65 4718592 576 4

    ==> changes in the free space can be noticed.

    SQL> Select TABLESPACE_NAME , FILE_ID,BYTES,BLOCK_id,blocks from dba_extents where segment_name=’EMP1′;

    TABLESPACE_NAME FILE_ID BYTES BLOCK_ID BLOCKS
    ————— ———- ———- ———- ———-
    USERS 4 65536 57 8

    SQL> Select TABLESPACE_NAME ,BYTES,blocks,extents from dba_segments where segment_name=’EMP1′;

    TABLESPACE_NAME BYTES BLOCKS EXTENTS
    ————— ———- ———- ———-
    USERS 65536 8 1

    ==> dropping table
    SQL> drop table emp1;

    Table dropped.

    ==>contents noticed in recyclebin
    SQL> show recyclebin
    ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
    —————- —————————— ———— ——————-
    EMP1 BIN$GW2nJ6wYNdXgRADgAMScEQ==$0 TABLE 2006-07-25:14:35:29

    ==> As object is dropped you can notice “no rows selected” from dba_extents
    SQL> Select TABLESPACE_NAME , FILE_ID,BYTES,BLOCK_id,blocks from dba_extents where segment_name=’EMP1′;

    no rows selected

    SQL> Select TABLESPACE_NAME , FILE_ID,BYTES,BLOCK_id,blocks from dba_extents where segment_name=’BIN$GW2nJ6wYNdXgRADgAMScEQ==$0′;

    no rows selected
    ==> In Oracle 10g when you drop the object it is renamed to BIN$ and it occupies the same space in the tablespace
    which can be noticed below
    SQL> select * from dba_free_space where tablespace_name=’USERS’;

    TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
    ————— ———- ———- ———- ———- ————
    USERS 4 65 4718592 576 4
    USERS 4 57 65536 8 4

    ==> As the name is renamed segment name will be BIN$ and so you can notice EMP1 is resulting in “no rows seelcted”
    SQL> Select TABLESPACE_NAME ,BYTES,blocks,extents from dba_segments where segment_name=’EMP1′;

    no rows selected

    ==> The same result can be noticed above with segment name = EMP1 which means the extents are not yet released
    or rather freespace is not reclaimed even though the object is dropped.
    SQL> Select TABLESPACE_NAME ,BYTES,blocks,extents from dba_segments
    where segment_name=’BIN$GW2nJ6wYNdXgRADgAMScEQ==$0′;

    TABLESPACE_NAME BYTES BLOCKS EXTENTS
    ————— ———- ———- ———-
    USERS 65536 8 1

    ==> purging recycle bin
    SQL> purge recyclebin;

    Recyclebin purged.

    ==> after purging the extents are released and free space is reclaimed which can be noticed
    by comparing the block_id,blocks,bytes from the first result.
    SQL> select * from dba_free_space where tablespace_name=’USERS’;

    TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
    ————— ———- ———- ———- ———- ————
    USERS 4 57 4784128 584 4

    ==> The extents are free
    SQL> Select TABLESPACE_NAME , FILE_ID,BYTES,BLOCK_id,blocks from dba_extents where segment_name=’EMP1′;

    no rows selected
    ==> Now obviously even when you specify BIN$ as segment_name “no rows selected” as free space is reclaimed.

    SQL> Select TABLESPACE_NAME ,BYTES,blocks,extents from dba_segments
    2 where segment_name=’BIN$GW2nJ6wYNdXgRADgAMScEQ==$0′;

    no rows selected

    ==> segments also returns “no rows selected” as EMP! is dropped and purged.
    SQL> Select TABLESPACE_NAME ,BYTES,blocks,extents from dba_segments where segment_name=’EMP1′;

    no rows selected
    Solution

    SOLUTION

    ————-

    CASE 1
    ———-
    As explained in the research section, from Oracle 10g the objects which are dropped are not really dropped it is renamed to some other name like BIN$%. So the space allocated to the object is not reclaimed until the object is purged from recyclebin, if the object is not purged then the space will not be reclaimed. The space