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!

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

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 which is occupied by this dropped object in recycle bin will be reclaimed only in case of any space crunch until then the freespace will not be reclaimed. As you can notify dba_free_space results as

    ==> 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

    If you notice after purging recyclebin the same query results in single row as

    ==> 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
    You can very well see that first row is not appearing after purging. This explains the scenario, so purging the recyclebin allows you to resize the tablespace.

    Case 2
    ———-
    The resizing of datafile might also fail with ORA-03297 when you try to resize the datafile below the highwatermark. So to find the limit of up to which you can resize, refer NOTE:130866.1 – How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark, which calculates highwatermark for the datafiles which will be helpful in resizing the datafile.

    If bigfile tablespace is used, it can be resized using alter tablespace resize ;
    or else it should be resized using alter database datafile ‘‘ resize ;

  2. admin says:

    Hdr: 5510991 10.2.0.1.0 RDBMS 10.2.0.1.0 SPACE PRODID-5 PORTID-23 5083393
    Abstract: WRONG OUTPUT FROM DBA_FREE_SPACE
    PROBLEM:
    ——–
    PROBLEM
    —————
    1. Desription of Problem

    Wrong output from DBA_FREE_SPACE

    4. Sequence of Events leading to the Problem

    Step to re-produce the problem

    Create 10 tables say table01, table02, table03.. table10 in same datafile
    lets say users01.dbf.
    Insert 500 records in all 10 tables in circular fashion (one record in each
    table at a time)
    Drop table table02, table04, table05, table06, table08, and table09

    List all the blocks (let?s call it list1) occupied by table01which is not
    dropped. To do so this is what we did
    a) Get the rowid for each record in table01.
    b) For each rowid get the block number using the dbms package
    dbms_rowid.rowid_block_number(rowid)

    List all the free blocks (let?s call it list2) for datafile users01.dbf using

    the following query on dba_free_space view
    Select block_id, blocks from dba_free_space where file_id=

    We saw that list2 (free block list) is having some of the blocks from list1
    which are the blocks having data.

    DIAGNOSTIC ANALYSIS:
    ——————–
    1. – verified issue during OWC.
    2. – checked for corruption using DBV – no errors.
    3. – checked w/a specified in Bug 5083393: no such line like: rb.file# =
    fi.relfile# in
    ORACLE_HOME/rd bms/admin/catspace.sql
    4. – Purged the recyclebin
    4. – there are multiple enteries in the dba_free_space for the same block_id
    5. – some of the free space records are overlapping
    6. – also noticed undropped table’s record’s blocks are in dba_free_space as
    free space

    WORKAROUND:
    ———–
    -None

    RELATED BUGS:
    ————-

    REPRODUCIBILITY:
    —————-

    TEST CASE:
    ———-

    STACK TRACE:
    ————
    -Stack Trace

    SUPPORTING INFORMATION:
    ———————–

    24 HOUR CONTACT INFORMATION FOR P1 BUGS:
    —————————————-
    24 HOUR CONTACT INFORMATION FOR P1 BUGS

    DIAL-IN INFORMATION:
    ——————–

    IMPACT DATE:
    ————
    This issue failed to reproduce under a testcase as generated by previous
    analysts.

    The ct uploaded a script that recreates the issue.

    This is located under bug5510991 as scr.tar

    The following are the ct instructions:

    01 scr1.sql – This script creates the 2 more datafiles with USERS
    tablespace.
    Please customize your datafile directory before
    running this Script.
    This also creates the 13 tables in USERS
    tablespaces.

    02 scr2.sh This is loop script, which will call scr3.sh
    adding 500 records in circular fashion.
    Customize the scr3.sh directory ? if current path is

    not included in the path list

    03 scr3.sh This is simple insert records in each table.

    04 scr5.sh This script updates one of the tables.

    Note : check the dba_free_space with temp01 table?s blocks now

    05 scr4.sh This script drops the tables.

    Note : check the dba_free_space with temp01 table?s blocks now

    01 check_script.sql – This scripts creates/spools 4 more tables. These
    scripts need to edited before running it . Replace ?SEL? with single quote &
    remove the blanks & No of record selected Line.

    By default, DBA_FREE_SPACE takes into account the recyclebin. Not all space
    in the recyclebin is reported as coalesced until the recyclebin is purged.
    That is why the DBA_FREE_SPACE_COALESCED view was introduced.

    I’m not showing any blocks in DBA_FREE_SPACE corresponding to any blocks
    in DBA_EXTENTS (keep in mind you must check both the block and file
    combination since there are multiple datafiles in the USERS tablespace).

    check_script.sql shows only the BLOCK_ID from the rows in the table;
    it doesn’t show the relative or absolute file numbers.

    I haven’t pasted any query output since it is so large, but I’ve
    containing the following files:

    blocks.sql — script to display the blocks in dba_free_space and
    dba_extents
    check2.sql — script to generate scripts to show the file and block#’s
    for the rows in tables temp1, temp3, temp7 and temp13
    blocks_before.log — output from blocks.sql before dropping tables
    blocks_after.log — output from blocks.sql after dropping tables
    So far I see no evidence of a bug.

  3. admin says:

    Hdr: 5083393 10.1.0.4 RDBMS 10.1.0.4 SPACE PRODID-5 PORTID-912
    Abstract: VALUE OF FILE_ID AND RELATIVE_FNO OF DBA_FREE_SPACE IS DIFFERENT
    PROBLEM:
    ——–
    When the table is dropped, the value of the FILE_ID column and
    the RELATIVE_FNO column of dba_free_space might be different.

    SQL> select * from dba_free_space where tablespace_name = ‘TBS_TEST1’;

    TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
    ————— ——- ——– ——– —— ————
    ?E
    ?E
    ?E
    TBS_TEST1 6 105 65536 8 6
    TBS_TEST1 6 113 65536 8 6
    TBS_TEST1 6 121 65536 8 6
    TBS_TEST1 6 9 65536 8 7 < --- TBS_TEST1 6 9 1048576 128 8 <--- TBS_TEST1 6 137 1048576 128 8 <--- TBS_TEST1 6 265 1048576 128 8 <--- TBS_TEST1 6 393 1048576 128 8 <--- TBS_TEST1 6 521 1048576 128 8 <--- TBS_TEST1 6 649 1048576 128 8 <--- When the number of data files is 1023 or less, FILE_ID and RELATIVE_FNO are recognition of becoming the same. DIAGNOSTIC ANALYSIS: -------------------- When the table that does DROP is done in PURGE, an free space comes to be displayed correctly. SQL> purge table tbs_test01;

    Table purged.

    SQL> select * from dba_free_space where tablespace_name = ‘TBS_TEST1′;

    TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
    ————— ——- ——– ——– —— ————
    TBS_TEST1 6 9 983040 120 6
    TBS_TEST1 7 9 983040 120 7
    TBS_TEST1 8 9 20905984 2552 8

    In a word, there seems to be a problem in the display of an free space
    concerning the segment that exists in RECYCLE BIN.

    WORKAROUND:
    ———–
    None

    RELATED BUGS:
    ————-
    None

    REPRODUCIBILITY:
    —————-
    100% (both In-house and Ct’ site)
    Rep? Platform RDBMS Ver.
    ——- ——————— ———-
    Y(100%) 912 Intel Windows NT 10.1.0.4
    Y(100%) 912 Intel Windows NT 10.1.0.5
    Y(100%) 912 Intel Windows NT 10.2.0.2
    Y(100%) 46 Linux x86 10.1.0.4

    TEST CASE:
    ———-

    STACK TRACE:
    ————
    None

    SUPPORTING INFORMATION:
    ———————–
    None

  4. admin says:

    Dba_free_space Return For Datafile Value Larger Than Datafile Size
    Applies to:
    Oracle Server – Enterprise Edition – Version: 10.2.0.1
    This problem can occur on any platform.
    This does not impact 9.2.x and eralier versions.
    Symptoms
    The free size of a datafile in a tablespace is more than the size of the datafile itself. The tablespace can be either in manual or auto segment space management, creating a table, populating with data and dropping it exhibits this anomaly.
    Changes

    Dropping table/objects from the tablespace.
    Cause

    Bug 5003087

    Abstract: DATAFILE FREE SPACE IS SHOWN GREATER THAN THE DATAFILE SIZE.

    Duplicate of following base

    Bug 5083393

    Details:
    This problem is introduced in 10.1.0.3 / 10.2.0.1 by the fix for bug 3492682.
    The values of FILE_ID / REL_FNO may be wrong in DBA_FREE_SPACE if a table has been dropped but exists in the RECYCLE BIN
    Workaround:
    Purge the dropped table from the recycle bin.

    When the table is dropped that the freed up space is accounted for in the wrong datafile and hence we see the size of datafile bigger than its defined size. If you purge the recyclebin then the incorrect accounting is fixed.
    Solution

    This bug is fixed in next release of Oracle 11 and is also fixed in upcoming patchsets 10.2.0.4 & 10.1.0.6. There are few one-off patches available, please check metalink for existence of one-off patch for your patchset level and OS platform.

    As a simple workaround you can purge the dropped table from the recycle bin.

  5. admin says:

    Queries on DBA_FREE_SPACE are Slow
    Applies To
    Oracle Server – Enterprise Edition – Version: 10.1.0.2
    This problem can occur on any platform.
    Symptoms
    1) Query on dba_free_space takes a long time

    2) Excessive IO waits are observed
    Cause
    1) In release 10G, the view dba_free_space is modified to access sys.recyclebin$ also.

    SQL> ;
    1* select text from dba_views where view_name=’DBA_FREE_SPACE’
    SQL> /

    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# = u.ktfbuesegtsn
    and u.ktfbuesegtsn = fi.ts#
    and u.ktfbuesegfno = 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) Large number of entries in sys.recyclebin$ can slow down the select on dba_free_space.

    3) This is a normal behavior.
    Fix
    Purge recyclebin.

    For example:

    SQL> purge recyclebin;
    Recyclebin purged.

    Or, as SYSDBA for system wide purging.

    SQL> purge dba_recyclebin;
    Recyclebin purged.

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569