考过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!
Resize Does Not Work Although Query Shows Free Space Available ORA-03297 resize size (BigFile Tablespace) results in ORA-03297 file contains blocks of data beyond requested RESIZE value, even though freespace is available.
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
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 ;‘ resize ;
or else it should be resized using alter database datafile ‘
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.
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
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.
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.