eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

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

DBA_FREE_SPACE的file_id和relative_fno问题

客户在检查数据库时发现了一个异常,在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

记录一下。



历史上的今天...
    >> 2011-12-22文章:
    >> 2008-12-22文章:
    >> 2007-12-22文章:
    >> 2006-12-22文章:
    >> 2005-12-22文章:
    >> 2004-12-22文章:

无觅

By eygle on 2010-12-22 14:51 | Comments (0) | FAQ | Oracle12c/11g | 2681 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com