注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Memory extender

Beautiful Day..

 
 
 

日志

 
 

HOW TO DISCOVER AND FIX THE MISTMATCH BETWEEN DBA_SEGMENTS AND DBA_EXTENTS DICTIONARY VIEWS  

2010-09-13 17:15:44|  分类: Oracle Database |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

<转>

HOW TO DISCOVER AND FIX THE MISTMATCH BETWEEN DBA_SEGMENTS AND DBA_EXTENTS DICTIONARY VIEWS [ID 463101.1]   

--------------------------------------------------------------------------------

  修改时间 21-NOV-2007     类型 HOWTO     状态 PUBLISHED   

In this Document
  Goal
  Solution
     What is the issue, impact, and known problems?
     How to discover the affected segments?
     How to fix the mismatch?



--------------------------------------------------------------------------------



Applies to:
Oracle Server - Enterprise Edition - Version: 9.2 to 10.2
Information in this document applies to any platform.

Goal
The purpose of this article is to give steps to discover the affected objects and fix the mismatch between dba_segments and dba_extents dictionary views. Solution
What is the issue, impact, and known problems?
Some types of DML/DDL operations (parallel index creation, frequent deletes/inserts) on segments can create mismatches in the bytes, blocks and extents columns reported in dba_segments and dba_extents dictionary. The mismatch mainly exists between segment header values and extent maps in the segment header. It is expected that individual values for each extent is correct but the summary of these values isn't reflected in the segment header due to a problem. The impact for this kind of mismatche is low on the database. Unless there are custom made monitoring tools relying on the dba_segments values, this situation would have no impact on the database, because no vital components are depending on it. Similar mismatches are reported in the following bugs: @Bug 6330259 SUM OF BYTE COLUMN IN DBA_SEGMENTS AND DBA_EXTENTS RETURNS DIFFERENT RESULT Bug 5665912 BYTES, # OF EXTENTS IN DBA_SEGMENTS NOT MATCH WITH DBA_EXTENTS Bug 4771672 DBA_SEGMENTS.BLOCKS WRONG AFTER PARALLEL CREATE INDEX Unless the problem matches any of the known problems, you need to monitor the segments and check if the issue reproduces consistently for a certain segment. If the issue reproduces in a segment repeatedly, please build a testcase, upload to support how data is being inserted/deleted or what type of statements (any DML/DDL) are being run against these segments. How to discover the affected segments?

The following query lists the segments affected by the mismatch for number of extents, blocks and bytes between segment header and extent maps.             select /*+ RULE */ s.tablespace_name, s.segment_name segment, s.partition_name, s.owner owner, s.segment_type, s.blocks sblocks, e.blocks eblocks, s.extents sextents, e.extents eextents, s.bytes sbytes, e.bytes ebytesfrom 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_name or s.partition_name is null) and s.segment_type = e.segment_type and s.owner not like 'SYS%' and ((s.blocks <> e.blocks) or (s.extents <> e.extents) or (s.bytes <> e.bytes));
  
  
  From the given list, find the row where the mismatch exists in blocks, bytes, extents columns. This may help to match the problem with any existing issue.How to fix the mismatch?
Once you identify the tablespace name from the list above, the values between dba_segments and dba_extents can be synchronized using TABLESPACE_FIX_SEGMENT_EXTBLKS to resolve the mismatch.               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.

  评论这张
 
阅读(413)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017