oracle的dba_ segment,dba_extents和dba_segments不一致问题及原因

dba_extents和dba_segments不一致问题及原因

日志。

Metalink Note 463101.1文章 HOW TO DISCOVER AND FIX THE MISTMATCH BETWEEN

DBA_SEGMENTS AND DBA_EXTENTS DICTIONARY VIEWS 描述了这一问题。

根据描述，这个问题主要发生在并行索引创建、频繁的DELETE/INSERT等操作中，主要原因是Segemnt Header信息未能及时更新，导致段头记录的空间值和Extent Map不一致

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.此外在某些从低版本升级到10g的系统可能会出现这个问题，在DMT到LMT的迁移中，可能在空间管理上出现Bug，引发问题。

Metalink提供如下一段代码用于检索可能存在问题的数据段：

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 ebytes

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_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)

);在我的一个Oracle 9i的系统中，找到了一个存在问题的对象：

SQL> col TABLESPACE_NAME for a20

SQL> col PARTITION_NAME for a10

SQL> SELECT /*+ RULE */

2         s.tablespace_name, s.segment_name SEGMENT, s.partition_name,

3         s.owner owner, s.segment_type, s.blocks sblocks, e.blocks eblocks,

s.extents sextents, e.extents eextents, s.BYTES sbytes, e.BYTES ebytes

4    FROM dba_segments s,

5         (SELECT   COUNT (*) extents, SUM (blocks) blocks, SUM (BYTES) BYTES,

segment_name, partition_name, segment_type, owner

FROM dba_extents

6    7    8    9          GROUP BY segment_name, partition_name, segment_type, owner) e

10   WHERE s.segment_name = e.segment_name

AND s.owner = e.owner

11   12     AND (s.partition_name = e.partition_name OR s.partition_name IS NULL)

13     AND s.segment_type = e.segment_type

14     AND s.owner NOT LIKE 'SYS%'

15     AND (   (s.blocks <> e.blocks)

16          OR (s.extents <> e.extents)

17          OR (s.BYTES <> e.BYTES)

18         );

TABLESPACE_NAME      SEGMENT              PARTITION_ OWNER      SEGMENT_TYPE       SBLOCKS    EBLOCKS   SEXTENTS

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

EEXTENTS     SBYTES     EBYTES

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

PUSHMOBIL            CM_APP_GATHERMOBILE             PUSHMOBIL  TABLE              1372700    1372704      85794

85794 1.1245E+10 1.1245E+10可是转储Segemnt Header信息，可以发现，段头的记录是正确的：

buffer tsn: 20 rdba: 0x0442f77b (17/194427)

scn: 0x081a.2f46a1d7 seq: 0x03 flg: 0x04 tail: 0xa1d72303

frmt: 0x02 chkval: 0x8002 type: 0x23=PAGETABLE SEGMENT HEADER

Extent Control Header

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

Extent Header:: spare1: 0      spare2: 0      #extents: 85794  #blocks: 1372704

last map  0x11005cd9  #maps: 169    offset: 2716

Highwater::  0x0d0f84b9  ext#: 85793  blk#: 16     ext size: 16

#blocks in seg. hdr's freelists: 0

#blocks below: 1367064

mapblk  0x11005cd9  offset: 142

Unlocked

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

Low HighWater Mark :

Highwater::  0x0d0f84b9  ext#: 85793  blk#: 16     ext size: 16

#blocks in seg. hdr's freelists: 0

#blocks below: 1372697

mapblk  0x11005cd9  offset: 142

Level 1 BMB for High HWM block: 0x0dce6ee9

Level 1 BMB for Low HWM block: 0x0dce6ee9

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

Segment Type: 1 nl2: 6      blksz: 8192   fbsz: 0

L2 Array start offset:  0x00001434

First Level 3 BMB:  0x00000000

L2 Hint for inserts:  0x0d0ebbea

Last Level 1 BMB:  0x0dce6ee9

Last Level II BMB:  0x0d0ebbea

Last Level III BMB:  0x00000000

Map Header:: next  0x0443f76a  #extents: 307  obj#: 31098  flag: 0x20000000

Extent Map

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

0x0442f779  length: 16这种情况，只能说明字典表seg$在内存中展示的数据与实际存储并不相符，这种不一致，重启数据库也许可以恢复一致；而10g中的大范围差别Oracle推荐用

DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS('tablespace_name');

修复，不过要慎重，这一不一致不会带来实质性的影响。

记录一下，有机会再细致研究！

-The End-

By eygle on 2009-08-04 09:58 |

Comments (6) |

FAQ | 2355 |

6 Comments

鄙人不才, 早年(1996)看到的Oracle文档将 Extent 翻译成 片(扩充片) 比较合适.

牛，1996年还没听说过Oracle。

片，没有流行起来！

再补充一句, Segment翻译成 "段", 这回公认了吧. ^_^

还有:

Extent 扩充,

Expand 扩展

MISTMATCH 这个单词是不是错了啊！~

MISTMATCH 这个单词是不是错了啊！~