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.
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.
The following query lists the segments affected by the mismatch for number of extents, blocks and bytes between segment header and extent maps.
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.
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.
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.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12798004/viewspace-2680783/，如需转载，请注明出处，否则将追究法律责任。