dba_extents和dba_segments不一致（转）
上一篇 /
下一篇 2008-04-17 14:17:35
/ 个人分类：Oracle专题
在不少production系统上看到
select sum(bytes) from dba_extents where wner=:o_name and segment_name=:seg_name;
select sum(bytes) from dba_segments where wner=:o_name and segment_name=:seg_name;
出来的结果不一致，而且有些表差别很大。那么究竟哪一个是准确的？
Metalink上有篇文章 HOW TO DISCOVER AND FIX THE MISTMATCH BETWEEN DBA_SEGMENTS AND DBA_EXTENTS DICTIONARY VIEWS （note id:463101.1）提到这一问题。
解释中说dba_extents中的值是可以依赖的，而dba_segments中的值可能会不准确。一些DDL/DML操作会导致其不一致，例如parallel index creation, frequent deletes/inserts。相关的还提到两个bug:
Bug 5665912 BYTES, # OF EXTENTS IN DBA_SEGMENTS NOT MATCH WITH DBA_EXTENTS
Bug 4771672 DBA_SEGMENTS.BLOCKS WRONG AFTER PARALLEL CREATE INDEX
你可以通过下面的SQL来找出不一致的记录:
- select
- /*+ RULE */ s.tablespace_name,s.segment_namesegment,s.partition_name,
- s.owner owner,s.segment_type,
- s.blocks sblocks,e.blockseblocks,
- s.extents sextents,e.extentseextents,s.bytessbytes,e.bytesebytes
- from
- dba_segments s,
- (select count(*)extents,sum(blocks)blocks,sum(bytes)bytes,segment_name,
- partition_name,segment_type,owner
- from dba_extents
- group
- by segment_name,partition_name,segment_type,owner)e
- where s.segment_name=e.segment_name
- and s.owner=e.owner
- and (s.partition_name=e.partition_nameors.partition_nameis
- null)
- and s.segment_type=e.segment_type
- and s.ownernotlike'SYS%'
- and ((s.blocks<>e.blocks)or(s.extents<>e.extents)or(s.bytes<>e.bytes));
解决方法是运行procedure: TABLESPACE_FIX_SEGMENT_EXTBLKS
DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS(’tablespace_name’);
Issuing DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS fixes the DBA_SEGMENTS values. The tablespace
must be kept online and read/write when this procedure is called.Runing this procedure requires COMPATIBLE parameter to be set to 10.0.0.0 or greater.
The procedure fixes extents, blocks and bytes in the segment headers to synchronize seg$ and
segment header entries.
It holds the allocation enqueue for the tablespace till the command is completed and this may delay some sort of operations in this tablespace (new extent allocation, deallocate extent, etc.). So it needs to be run during an idle period.
对于application并没有什么影响，只是会影响一些依赖于dba_segments做监控的monitoring tool。
导入论坛
引用链接
收藏
分享给好友
推荐到圈子
管理
举报
TAG:
