DBA_FREE_SPACE的file_id和relative_fno问题

« O3SP - 深入解析Oracle - 我的主题演讲 | Blog首页 | 圣诞快乐新年快乐 - 祝愿梦想能够成真 »

客户在检查数据库时发现了一个异常，在DBA_FREE_SPACE中的file_id和relative_fno不一致，即存在如下现象：

SQL> select * from dba_data_files where file_id <> relative_fno;
no rows selected

SQL> select count(*) from dba_free_space where file_id <> relative_fno;

COUNT(*)
----------
1194

看到这个问题，第一直接是跟recyclebin有关，因为DBA_FREE_SPACE和recyclebin相关的问题太多了，在我的书里已经写过几个相关的案例了。

DBA_FREE_SPACE的视图定义如下，Oracle将recyclebin的对象也纳入进来：

create or replace view DBA_FREE_SPACE
    (TABLESPACE_NAME, FILE_ID, BLOCK_ID,
     BYTES, BLOCKS, RELATIVE_FNO)
as
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 rb.file# = 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
/

在我的一个10.2.0.3的数据库中，发现同样存在类似的问题：

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 22 13:57:26 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options

SQL> select count(*) from dba_free_space where file_id<>RELATIVE_FNO;

COUNT(*)
----------
3

将DBA_FREE_SPACE的语句解析出来，更改一下条件执行：

SQL> select /*+ ordered use_nl(u) use_nl(fi) */
2         ts.name, fi.file#, u.ktfbuebno,
3         u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
4 from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
5 where ts.ts# = rb.ts#
6    and rb.ts# = fi.ts#
7    and rb.file# = fi.relfile#
8    and u.ktfbuesegtsn = rb.ts#
9    and u.ktfbuesegfno = rb.file#
10    and u.ktfbuesegbno = rb.block#
11    and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
12    and fi.file# <> u.ktfbuefno;

NAME                   FILE# KTFBUEBNO U.KTFBUEBLKS*TS.BLOCKSIZE KTFBUEBLKS KTFBUEFNO
--------------------- ---------- ---------- ------------------------- ---------- ----------
SMSNP                      11    3085273                     65536          8          9
SMSNP                        6     332961                     65536          8          9
SMSNP                  12    3107745                     65536          8          6

我们发现正式回收站带来的3个异常区间，由于这里的相对文件号来自ktfbuefno，所以和其他视图并不一致相等。

尝试清空回收站，问题消除：

SQL> show user
USER is "SYS"
SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> select /*+ ordered use_nl(u) use_nl(fi) */
2         ts.name, fi.file#, u.ktfbuebno,
3         u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
4 from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
5 where ts.ts# = rb.ts#
6    and rb.ts# = fi.ts#
7    and rb.file# = fi.relfile#
8    and u.ktfbuesegtsn = rb.ts#
9    and u.ktfbuesegfno = rb.file#
10    and u.ktfbuesegbno = rb.block#
11    and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
12    and fi.file# <> u.ktfbuefno;

no rows selected

SQL> select count(*) from dba_free_space where file_id<>RELATIVE_FNO;

COUNT(*)
----------
         0