首页 » ORACLE 9i-23ai » HWM 高水位储存位置

HWM 高水位储存位置

Last login: Fri Jul 15 10:19:41 2011 from 192.168.3.226
[oracle@orazhang ~]$ ora

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 7月 18 11:23:11 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@ORCL> conn anbob/anbob
Connected.
anbob@ORCL> create table testhwm(id int,name varchar2(4000),remark  varchar2(4000));

Table created.

anbob@ORCL> conn system/oracle
Connected.
system@ORCL> select extent_id,file_id,block_id,blocks from dba_extents where segment_name='TESTHWM';

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          9        873          8

system@ORCL> begin
  2  for i in 1..10000
  3  loop
  4   insert into testhwm(id,name) values(i,'anbob'||i);
  5  end loop;
  6  end;
  7  /
 insert into testhwm(id,name) values(i,'anbob'||i);
             *
ERROR at line 4:
ORA-06550: line 4, column 14:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 4, column 2:
PL/SQL: SQL Statement ignored

system@ORCL> l4
  4*  insert into testhwm(id,name) values(i,'anbob'||i);
system@ORCL> c/testhwm/anbob.testhwm/
  4*  insert into anbob.testhwm(id,name) values(i,'anbob'||i);
system@ORCL> l
  1  begin
  2  for i in 1..10000
  3  loop
  4   insert into anbob.testhwm(id,name) values(i,'anbob'||i);
  5  end loop;
  6* end;
system@ORCL> /

PL/SQL procedure successfully completed.

system@ORCL> select extent_id,file_id,block_id,blocks from dba_extents where segment_name='TESTHWM';

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          9        873          8
         1          9        889          8
         2          9        897          8
         3          9        905          8

system@ORCL> select header_file,header_block,blocks,extents from dba_segments where segment_name='TESTHWM';

HEADER_FILE HEADER_BLOCK     BLOCKS    EXTENTS
----------- ------------ ---------- ----------
          9          875         32          4

system@ORCL> alter system dump datafile 9 block 873;

System altered.

system@ORCL> host

[oracle@orazhang ~]$ cd /u01/app/oracle/admin/orcl/udump/

[oracle@orazhang udump]$vi orcl_ora_3798.trc

*** 2011-07-18 11:58:13.292
*** ACTION NAME:() 2011-07-18 11:58:13.280
*** MODULE NAME:(SQL*Plus) 2011-07-18 11:58:13.280
*** SERVICE NAME:(SYS$USERS) 2011-07-18 11:58:13.280
*** SESSION ID:(154.20) 2011-07-18 11:58:13.280
Start dump data blocks tsn: 9 file#: 9 minblk 873 maxblk 873
buffer tsn: 9 rdba: 0x02400369 (9/873)
scn: 0x0000.4fd81aa7 seq: 0x02 flg: 0x04 tail: 0x1aa72002
frmt: 0x02 chkval: 0xdb3d type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D4A4C00 to 0x0D4A6C00
D4A4C00 0000A220 02400369 4FD81AA7 04020000  [ ...i.@....O....]
D4A4C10 0000DB3D 00000000 00000000 00000000  [=...............]
D4A4C20 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
D4A4C40 00000000 00000000 00000000 00000004  [................]
/u01/app/oracle/admin/orcl/udump/orcl_ora_3798.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      orazhang
Release:        2.6.18-53.el5xen
Version:        #1 SMP Wed Oct 10 17:06:12 EDT 2007
Machine:        i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 3798, image: oracle@orazhang (TNS V1-V3)

*** 2011-07-18 11:58:13.292
*** ACTION NAME:() 2011-07-18 11:58:13.280
*** MODULE NAME:(SQL*Plus) 2011-07-18 11:58:13.280
*** SERVICE NAME:(SYS$USERS) 2011-07-18 11:58:13.280
*** SESSION ID:(154.20) 2011-07-18 11:58:13.280
Start dump data blocks tsn: 9 file#: 9 minblk 873 maxblk 873
buffer tsn: 9 rdba: 0x02400369 (9/873)
scn: 0x0000.4fd81aa7 seq: 0x02 flg: 0x04 tail: 0x1aa72002
frmt: 0x02 chkval: 0xdb3d type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D4A4C00 to 0x0D4A6C00
D4A4C00 0000A220 02400369 4FD81AA7 04020000  [ ...i.@....O....]
D4A4C10 0000DB3D 00000000 00000000 00000000  [=...............]
D4A4C20 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
D4A4C40 00000000 00000000 00000000 00000004  [................]
D4A4C50 FFFFFFFF 00000000 00000003 00000010  [................]
D4A4C60 00010002 00000000 00000000 00000000  [................]
D4A4C70 00000000 00000010 4E23A83D 4E23A83D  [........=.#N=.#N]
D4A4C80 00000000 00000000 00000000 00000000  [................]
D4A4C90 0240036A 00000000 00000000 00000000  [j.@.............]
D4A4CA0 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
D4A4CC0 000112DB 00000000 00000000 02400369  [............i.@.]
D4A4CD0 00000008 00000000 02400379 00000008  [........y.@.....]
D4A4CE0 00000008 00000000 00000000 00000000  [................]
D4A4CF0 00000000 00000000 00000000 00000000  [................]
        Repeat 8 times
D4A4D80 00000000 00000000 00000000 11111111  [................]
D4A4D90 11111111 00000000 00000000 00000000  [................]
D4A4DA0 00000000 00000000 00000000 00000000  [................]
        Repeat 484 times
D4A6BF0 00000000 00000000 00000000 1AA72002  [............. ..]
Dump of First Level Bitmap Block
 --------------------------------
   nbits : 4 nranges: 2         parent dba:  0x0240036a   poffset: 0
   unformatted: 0       total: 16        first useful block: 3
   owning instance : 1
   instance ownership changed at 07/18/2011 11:27:57
   Last successful Search 07/18/2011 11:27:57
   Freeness Status:  nf1 0      nf2 0      nf3 0      nf4 0

   Extent Map Block Offset: 4294967295
   First free datablock : 16
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
[oracle@orazhang udump]$ ora

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 7月 18 14:15:26 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@ORCL> alter system dump datafile 9 block 874;

System altered.

[oracle@orazhang udump]$ vi /u01/app/oracle/admin/orcl/udump/orcl_ora_4401.trc

*** 2011-07-18 14:16:13.223
*** SERVICE NAME:(SYS$USERS) 2011-07-18 14:16:13.223
*** SESSION ID:(138.104) 2011-07-18 14:16:13.223
Start dump data blocks tsn: 9 file#: 9 minblk 874 maxblk 874
buffer tsn: 9 rdba: 0x0240036a (9/874)
scn: 0x0000.4fd81abf seq: 0x03 flg: 0x04 tail: 0x1abf2103
frmt: 0x02 chkval: 0xdacd type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0E095C00 to 0x0E097C00
E095C00 0000A221 0240036A 4FD81ABF 04030000  [!...j.@....O....]
E095C10 0000DACD 00000000 00000000 00000000  [................]
E095C20 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
E095C40 00000000 00000000 00000000 0240036B  [............k.@.]
E095C50 00000002 00000001 00000001 00000000  [................]
E095C60 00000000 00000000 000112DB 00000001  [................]
E095C70 00000000 02400369 00010001 02400381  [....i.@.......@.]
E095C80 00010005 00000000 00000000 00000000  [................]
E095C90 00000000 00000000 00000000 00000000  [................]
        Repeat 501 times
E097BF0 00000000 00000000 00000000 1ABF2103  [.............!..]
Dump of Second Level Bitmap Block
   number: 2       nfree: 1       ffree: 1      pdba:     0x0240036b
   Inc #: 0 Objd: 70363
  opcode:0
 xid:
  L1 Ranges :
  --------------------------------------------------------
   0x02400369  Free: 1 Inst: 1

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 7月 18 14:18:37 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@ORCL> alter system dump datafile 9 block 875;

System altered.

[oracle@orazhang udump]$ vi /u01/app/oracle/admin/orcl/udump/orcl_ora_4412.trc

*** SERVICE NAME:(SYS$USERS) 2011-07-18 14:18:43.156
*** SESSION ID:(138.107) 2011-07-18 14:18:43.156
Start dump data blocks tsn: 9 file#: 9 minblk 875 maxblk 875
buffer tsn: 9 rdba: 0x0240036b (9/875)
scn: 0x0000.4fd81ac3 seq: 0x01 flg: 0x04 tail: 0x1ac32301
frmt: 0x02 chkval: 0xf76f type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0E353C00 to 0x0E355C00
E353C00 0000A223 0240036B 4FD81AC3 04010000  [#...k.@....O....]
E353C10 0000F76F 00000000 00000000 00000000  [o...............]
E353C20 00000000 00000004 00000020 00000A9C  [........ .......]
E353C30 00000003 00000008 00000008 02400391  [..............@.]
E353C40 00000000 00000003 00000000 0000001C  [................]
E353C50 00000000 00000000 00000000 00000003  [................]
E353C60 00000008 00000008 02400391 00000000  [..........@.....]
E353C70 00000003 00000000 0000001C 02400381  [..............@.]
E353C80 02400381 00000000 00000000 00000000  [..@.............]
E353C90 00000000 00000000 00000000 00000000  [................]
        Repeat 3 times
E353CD0 00000001 00002000 00000000 00001434  [..... ......4...]
E353CE0 00000000 0240036A 00000001 02400381  [....j.@.......@.]
E353CF0 0240036A 00000000 00000000 00000000  [j.@.............]
E353D00 00000000 00000000 00000004 00000000  [................]
E353D10 000112DB 10000000 02400369 00000008  [........i.@.....]
E353D20 02400379 00000008 02400381 00000008  [y.@.......@.....]
E353D30 02400389 00000008 00000000 00000000  [..@.............]
E353D40 00000000 00000000 00000000 00000000  [................]
        Repeat 150 times
E3546B0 02400369 0240036C 02400369 02400379  [i.@.l.@.i.@.y.@.]
E3546C0 02400381 02400382 02400381 02400389  [..@...@...@...@.]
E3546D0 00000000 00000000 00000000 00000000  [................]
        Repeat 150 times
E355040 00000000 00000000 0240036A 00000000  [........j.@.....]
E355050 00000000 00000000 00000000 00000000  [................]
        Repeat 185 times
E355BF0 00000000 00000000 00000000 1AC32301  [.............#..]
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 4      #blocks: 32
                  last map  0x00000000  #maps: 0      offset: 2716
      Highwater::  0x02400391  ext#: 3      blk#: 8      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 28
  mapblk  0x00000000  offset: 3
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x02400391  ext#: 3      blk#: 8      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 28
  mapblk  0x00000000  offset: 3
  Level 1 BMB for High HWM block: 0x02400381
  Level 1 BMB for Low HWM block: 0x02400381
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x0240036a
  Last Level 1 BMB:  0x02400381
  Last Level II BMB:  0x0240036a
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 4    obj#: 70363  flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x02400369  length: 8
   0x02400379  length: 8
   0x02400381  length: 8
   0x02400389  length: 8

  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x02400369 Data dba:  0x0240036c
   Extent 1     :  L1 dba:  0x02400369 Data dba:  0x02400379
   Extent 2     :  L1 dba:  0x02400381 Data dba:  0x02400382
   Extent 3     :  L1 dba:  0x02400381 Data dba:  0x02400389
  --------------------------------------------------------

   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x0240036a

End dump data blocks tsn: 9 file#: 9 minblk 875 maxblk 875

sys@ORCL> select object_name from dba_objects where object_id=70363;
OBJECT_NAME
------------------------------
TESTHWM

note:
    高水位记录在segment hearder的第一个block中，而非segment 第一个区的第一个block,有上面可以看到，segment 中分配第1,2个block
分别记录的是first\SECOND LEVEL BITMAP BLOCK,extent分配不连续，但所隐含的block理论上是连续的
打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Sofia Pakonen | #1
    2011-12-21 at 09:24

    The subsequent time I learn a weblog, I hope that it doesnt disappoint me as a lot as this one. I mean, I know it was my option to learn, however I actually thought youd have one thing interesting to say. All I hear is a bunch of whining about something that you can repair when you werent too busy on the lookout for attention.

上一篇：

下一篇：