DBA_FREE_SPACE的file_id和relative_fno问题
来源:互联网 发布:percircle.js 编辑:程序博客网 时间:2024/06/08 05:34
客户在检查数据库时发现了一个异常,在DBA_FREE_SPACE中的file_id和relative_fno不一致,即存在如下现象:
DBA_FREE_SPACE的视图定义如下,Oracle将recyclebin的对象也纳入进来:
在我的一个10.2.0.3的数据库中,发现同样存在类似的问题:
将DBA_FREE_SPACE的语句解析出来,更改一下条件执行:
我们发现正式回收站带来的3个异常区间,由于这里的相对文件号来自ktfbuefno,所以和其他视图并不一致相等。
尝试清空回收站,问题消除:
记录一下。
SQL> select * from dba_data_files where file_id <> relative_fno;看到这个问题,第一直接是跟recyclebin有关,因为DBA_FREE_SPACE和recyclebin相关的问题太多了,在我的书里已经写过几个相关的案例了。
no rows selected
SQL> select count(*) from dba_free_space where file_id <> relative_fno;
COUNT(*)
----------
1194
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
记录一下。
- DBA_FREE_SPACE的file_id和relative_fno问题
- file_id和relative_fno的区别
- dba_free_space中同一个file_id存在多条记录的问题
- 数据文件个数大于1024时ORACLE数据文件FILE_ID及RELATIVE_FNO的变化示例
- oracle 10g recyclebin引起的dba_free_space性能问题
- ORA-01653/01654错误和dba_free_space视图的理解
- rdba地址如何计算出file_id和block_id
- dba_free_space 以及查询剩余表空间百分比和回滚段命中率的SQL
- DBA_FREE_SPACE查询慢的原因及解决方法
- ORACLE dba_free_space中没有要找的表空间名字
- 怎么将dump 出来的 bitmap index 中的rowid 还原回 file_id,block_id
- dba_free_space 等表空间的视图不能在过程中被使用的问题解决
- (2011-01-27)通过dba_free_space视图查看表空间的空间分配过程
- Oracle dba_data_files dba_segments dba_free_space
- ORACLE 根据file_id block_id 查询表名
- 6.Oracle杂记——数据字典dba_free_space
- forward和redirect的问题(路径问题)
- 海盗问题和汉诺塔问题的分析
- Android异步下载网络图片(其二)
- 我的大三生活
- for语句计算1到10的阶乘的和
- 学习汇编的终极圣地
- Android编译重要参数LOCAL_MODULE_TAGS
- DBA_FREE_SPACE的file_id和relative_fno问题
- MessageBox in wpf
- Spring 3 MVC深入研究
- jQuery选择器的性能测试
- SDRAM内存驱动 的学习
- GridView 单击选择行,双击打开详细页面,鼠标移到某行上变色
- 关于ExtJS错误“例外被抛出且未被接住”问题
- 一片关于poi 读取xls写入数据库的文档
- Struts2中DispatherAction的功能