1. 测试基本脚本
create tablespace assm datafile '/data1/oracle/oradata/assm01.dbf' size extent management local uniform size 40K ----这里对于不同size分别测试 segment space management auto / CREATE TABLE TEST_HWM1 (ID CHAR(2000), NAME CHAR(2000) ) Tablespace ASSM STORAGE ( MINEXTENTS 2) PCTFREE 40 / select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS from dba_extents where segment_name='TEST_HWM1' / alter system dump datafile 7 block min 9 block max 11; insert into test_hwm1 values('1','eeyglee'); commit; alter system dump datafile 7 block min 9 block max 11; drop tablespace assm including contents and datafiles; |
2. 测试步骤
[oracle@jumper udump]$ sqlplus "/ as sysdba" SQL*Plus: Release Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release SQL> drop tablespace assm including contents and datafiles; Tablespace dropped. SQL> create tablespace assm 2 datafile '/data1/oracle/oradata/assm01.dbf' 3 size 4 extent management local uniform size 40K 5 segment space management auto 6 / Tablespace created. SQL> CREATE TABLE TEST_HWM1 (ID CHAR(2000), NAME CHAR(2000) ) 2 Tablespace ASSM 3 STORAGE ( MINEXTENTS 2) PCTFREE 40 4 / Table created. SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents 3 where segment_name='TEST_HWM1' 4 / EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 7 7 9 5 1 7 7 14 5 SQL> ! vi a.sql alter system dump datafile 7 block min 9 block max 11; insert into test_hwm1 values('1','eeyglee'); commit; alter system dump datafile 7 block min 9 block max 11; ~ "a.sql" [New] SQL> set echo on SQL> @a SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> SQL> insert into test_hwm1 values('1','eeyglee'); 1 row created. SQL> commit; Commit complete. SQL> SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> ! [oracle@jumper udump]$ ls a.sql hsjf_ora_14222.trc [oracle@jumper udump]$ more *.trc /opt/oracle/admin/hsjf/udump/hsjf_ora_14222.trc Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release ORACLE_HOME = /opt/oracle/product/ System name: Linux Node name: jumper.hurray.com.cn Release: Version: #1 Wed Sep 4 13:35:50 EDT 2002 Machine: i686 Instance name: hsjf Redo thread mounted by this instance: 1 Oracle process number: 9 Unix process pid: 14222, image: oracle@jumper.hurray.com.cn (TNS V1-V3) *** 2004-06-15 11:03:09.785 *** SESSION ID:(8.1436) 2004-06-15 11:03:09.784 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444b37 seq: 0x02 flg: 0x00 tail: 0x4b372002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x unformatted: 7 total: 10 first useful block: 3 owning instance : 1 instance ownership changed at Last successful Search Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295 First free datablock : 3 Bitmap block lock opcode 3 Locker xid: : 0x Highwater:: 0x #blocks in seg. hdr's freelists: 0 ---文件头 8 #blocks below: 0 ---Metadata 3 mapblk 0x00000000 offset: 0 ---HWM = 8 + 3 + 1 = 12 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x 0x
0:Metadata 1:Metadata 2:Metadata 3:unformatted 4:unformatted 5:unformatted 6:unformatted 7:unformatted 8:unformatted 9:unformatted -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444b36 seq: 0x02 flg: 0x00 tail: 0x4b362102 frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444b frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 10 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15685 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444b46 seq: 0x04 flg: 0x00 tail: 0x4b462004 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x unformatted: 5 total: 10 first useful block: 3 owning instance : 1 instance ownership changed at 06/15/2004 11:03:09 Last successful Search 06/15/2004 11:03:09 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 1
Extent Map Block Offset: 4294967295 First free datablock : 3 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 HWM = 8 + 3 + 2 +1 = 14 #blocks below: 2 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x 0x
0:Metadata 1:Metadata 2:Metadata 3:75-100% free 此处一次格式化 4:25-50% free 5:unformatted 6:unformatted 7:unformatted extents另外2个block 8:unformatted 9:unformatted -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444b36 seq: 0x02 flg: 0x00 tail: 0x4b362102 frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444b46 seq: 0x01 flg: 0x00 tail: 0x4b462301 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 10 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 2 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15685 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 |
继续增加记录
[oracle@jumper udump]$ more hsjf_ora_16003.trc /opt/oracle/admin/hsjf/udump/hsjf_ora_16003.trc Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release ORACLE_HOME = /opt/oracle/product/ System name: Linux Node name: jumper.hurray.com.cn Release: Version: #1 Wed Sep 4 13:35:50 EDT 2002 Machine: i686 Instance name: hsjf Redo thread mounted by this instance: 1 Oracle process number: 9 Unix process pid: 16003, image: oracle@jumper.hurray.com.cn (TNS V1-V3) *** SESSION ID:(8.1438) 2004-06-15 11:14:10.331 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x unformatted: 0 total: 10 first useful block: 3 owning instance : 1 instance ownership changed at 06/15/2004 11:14:07 Last successful Search 06/15/2004 11:14:07 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 4
Extent Map Block Offset: 4294967295 First free datablock : 5 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 HWM = H13 = 19 #blocks below: 7 HWM=19=8 + 3 + 2 + 5 + 1 =19 mapblk 0x00000000 offset: 1 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x 0x ---Oracle选择格式化第二个extents 5个block 0:Metadata 1:Metadata 2:Metadata 3:FULL 4:FULL 5:75-100% free 6:75-100% free 7:75-100% free 8:75-100% free 9:25-50% free -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444b36 seq: 0x02 flg: 0x00 tail: 0x4b362102 frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 10 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 7 mapblk 0x00000000 offset: 1 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15685 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 |
测试第二种情况:
[oracle@jumper udump]$ sqlplus "/as sysdba" SQL*Plus: Release Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release SQL> drop tablespace assm including contents and datafiles; Tablespace dropped. SQL> @a SQL> create tablespace assm 2 datafile '/data1/oracle/oradata/assm01.dbf' 3 size 4 extent management local uniform size 64K 5 segment space management auto 6 / Tablespace created. SQL> SQL> CREATE TABLE TEST_HWM1 (ID CHAR(2000), NAME CHAR(2000) ) 2 Tablespace ASSM 3 STORAGE ( MINEXTENTS 2) PCTFREE 40 4 / Table created. SQL> SQL> SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents 3 where segment_name='TEST_HWM1' 4 / EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 7 7 9 8 1 7 7 17 8 SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> SQL> insert into test_hwm1 values('1','eeyglee'); 1 row created. SQL> commit; Commit complete. SQL> SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> exit Disconnected from Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release [oracle@jumper udump]$ ls -sort total 60 16 -rw-r----- 1 oracle 15744 6月 15 11:13 hsjf_ora_14222.trc 4 -rw-r--r-- 1 oracle 109 6月 15 11:14 b.sql 12 -rw-r----- 1 oracle 11888 6月 15 11:14 hsjf_ora_16003.trc 4 -rw-r--r-- 1 oracle 546 6月 15 11: 12 -rw-r----- 1 oracle 8325 6月 15 11:22 hsjf_ora_16983.trc 12 -rw-r----- 1 oracle 8326 6月 15 11:26 hsjf_ora_17568.trc [oracle@jumper udump]$ more hsjf_ora_17568.trc /opt/oracle/admin/hsjf/udump/hsjf_ora_17568.trc Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release ORACLE_HOME = /opt/oracle/product/ System name: Linux Node name: jumper.hurray.com.cn Release: Version: #1 Wed Sep 4 13:35:50 EDT 2002 Machine: i686 Instance name: hsjf Redo thread mounted by this instance: 1 Oracle process number: 10 Unix process pid: 17568, image: oracle@jumper.hurray.com.cn (TNS V1-V3) *** SESSION ID:(11.587) 2004-06-15 11:26:20.196 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444d6b seq: 0x02 flg: 0x00 tail: 0x4d6b2002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x unformatted: 13 total: 16 first useful block: 3 owning instance : 1 instance ownership changed at Last successful Search Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295 First free datablock : 3 Bitmap block lock opcode 3 Locker xid: : 0x0009.020.00000aca Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x 0x
0:Metadata 1:Metadata 2:Metadata 3:unformatted 4:unformatted 5:unformatted 6:unformatted 7:unformatted 8:unformatted 9:unformatted 10:unformatted 11:unformatted 12:unformatted 13:unformatted 14:unformatted 15:unformatted -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d6e seq: 0x01 flg: 0x00 tail: 0x4d6e2301 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15687 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444d71 seq: 0x04 flg: 0x00 tail: 0x4d712004 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x unformatted: 8 total: 16 first useful block: 3 owning instance : 1 instance ownership changed at 06/15/2004 11:26:20 Last successful Search 06/15/2004 11:26:20 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 4
Extent Map Block Offset: 4294967295 First free datablock : 3 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x 0x
0:Metadata 1:Metadata 2:Metadata 3:25-50% free --5个block被格式化 4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free 8:unformatted 9:unformatted 10:unformatted 11:unformatted 12:unformatted 13:unformatted 14:unformatted 15:unformatted -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d71 seq: 0x01 flg: 0x00 tail: 0x4d712301 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15687 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 [oracle@jumper udump]$ sqlplus "/ as sysdba" SQL*Plus: Release Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release SQL> @b SQL> insert into test_hwm1 values('1','eeyglee'); 1 row created. SQL> commit; Commit complete. SQL> SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> @b SQL> set echo on SQL> insert into test_hwm1 values('1','eeyglee'); 1 row created. SQL> commit; Commit complete. SQL> SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> @b SQL> set echo on SQL> insert into test_hwm1 values('1','eeyglee'); 1 row created. SQL> commit; Commit complete. SQL> SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> @b SQL> set echo on SQL> insert into test_hwm1 values('1','eeyglee'); 1 row created. SQL> commit; Commit complete. SQL> SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> @b SQL> set echo on SQL> insert into test_hwm1 values('1','eeyglee'); 1 row created. SQL> commit; Commit complete. SQL> SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> @b SQL> set echo on SQL> insert into test_hwm1 values('1','eeyglee'); 1 row created. SQL> commit; Commit complete. SQL> SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> ! [oracle@jumper udump]$ ls -sort total 84 16 -rw-r----- 1 oracle 15744 6月 15 11:13 hsjf_ora_14222.trc 12 -rw-r----- 1 oracle 11888 6月 15 11:14 hsjf_ora_16003.trc 4 -rw-r--r-- 1 oracle 546 6月 15 11: 12 -rw-r----- 1 oracle 8325 6月 15 11:22 hsjf_ora_16983.trc 12 -rw-r----- 1 oracle 8326 6月 15 11:26 hsjf_ora_17568.trc 4 -rw-r--r-- 1 oracle 121 6月 15 11:29 b.sql 24 -rw-r----- 1 oracle 23800 6月 15 11:29 hsjf_ora_18039.trc [oracle@jumper udump]$ more hsjf_ora_18039.trc /opt/oracle/admin/hsjf/udump/hsjf_ora_18039.trc Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release ORACLE_HOME = /opt/oracle/product/ System name: Linux Node name: jumper.hurray.com.cn Release: Version: #1 Wed Sep 4 13:35:50 EDT 2002 Machine: i686 Instance name: hsjf Redo thread mounted by this instance: 1 Oracle process number: 10 Unix process pid: 18039, image: oracle@jumper.hurray.com.cn (TNS V1-V3) *** SESSION ID:(11.591) 2004-06-15 11:29:15.312 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444dab seq: 0x02 flg: 0x00 tail: 0x4dab2002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x unformatted: 8 total: 16 first useful block: 3 owning instance : 1 instance ownership changed at 06/15/2004 11:26:20 Last successful Search 06/15/2004 11:26:20 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 3
Extent Map Block Offset: 4294967295 First free datablock : 4 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x 0x
0:Metadata 1:Metadata 2:Metadata 3:FULL 4:75-100% free 5:75-100% free 6:25-50% free 7:75-100% free 8:unformatted 9:unformatted 10:unformatted 11:unformatted 12:unformatted 13:unformatted 14:unformatted 15:unformatted -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d71 seq: 0x01 flg: 0x00 tail: 0x4d712301 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15687 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444dad seq: 0x02 flg: 0x00 tail: 0x4dad2002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x unformatted: 8 total: 16 first useful block: 3 owning instance : 1 instance ownership changed at 06/15/2004 11:26:20 Last successful Search 06/15/2004 11:26:20 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 2
Extent Map Block Offset: 4294967295 First free datablock : 4 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x 0x
0:Metadata 1:Metadata 2:Metadata 3:FULL 4:75-100% free 5:75-100% free 6:FULL 7:25-50% free 8:unformatted 9:unformatted 10:unformatted 11:unformatted 12:unformatted 13:unformatted 14:unformatted 15:unformatted -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d71 seq: 0x01 flg: 0x00 tail: 0x4d712301 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15687 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444daf seq: 0x02 flg: 0x00 tail: 0x4daf2002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x unformatted: 8 total: 16 first useful block: 3 owning instance : 1 instance ownership changed at 06/15/2004 11:26:20 Last successful Search 06/15/2004 11:26:20 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 1
Extent Map Block Offset: 4294967295 First free datablock : 4 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x 0x
0:Metadata 1:Metadata 2:Metadata 3:FULL 4:25-50% free 5:75-100% free 6:FULL 7:FULL 8:unformatted 9:unformatted 10:unformatted 11:unformatted 12:unformatted 13:unformatted 14:unformatted 15:unformatted -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d71 seq: 0x01 flg: 0x00 tail: 0x4d712301 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15687 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444db1 seq: 0x02 flg: 0x00 tail: 0x4db12002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x unformatted: 8 total: 16 first useful block: 3 owning instance : 1 instance ownership changed at 06/15/2004 11:26:20 Last successful Search 06/15/2004 11:26:20 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 0
Extent Map Block Offset: 4294967295 First free datablock : 5 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x 0x
0:Metadata 1:Metadata 2:Metadata 3:FULL 4:FULL 5:25-50% free 6:FULL 7:FULL 8:unformatted 9:unformatted 10:unformatted 11:unformatted 12:unformatted 13:unformatted 14:unformatted 15:unformatted -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d71 seq: 0x01 flg: 0x00 tail: 0x4d712301 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15687 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444db3 seq: 0x04 flg: 0x00 tail: 0x4db32004 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x unformatted: 0 total: 16 first useful block: 3 owning instance : 1 instance ownership changed at 06/15/2004 11:29:19 Last successful Search 06/15/2004 11:29:19 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 7
Extent Map Block Offset: 4294967295 First free datablock : 8 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 13 mapblk 0x00000000 offset: 1 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x 0x
0:Metadata 1:Metadata 2:Metadata 3:FULL --格式化第二个extetns8个block 4:FULL 5:FULL 6:FULL 7:FULL 8:75-100% free 9:75-100% free 10:25-50% free 11:75-100% free 12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444db3 seq: 0x01 flg: 0x00 tail: 0x4db32301 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 13 mapblk 0x00000000 offset: 1 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15687 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444db5 seq: 0x02 flg: 0x00 tail: 0x4db52002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x unformatted: 0 total: 16 first useful block: 3 owning instance : 1 instance ownership changed at 06/15/2004 11:29:19 Last successful Search 06/15/2004 11:29:19 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 6
Extent Map Block Offset: 4294967295 First free datablock : 8 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 13 mapblk 0x00000000 offset: 1 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x 0x
0:Metadata 1:Metadata 2:Metadata 3:FULL 4:FULL 5:FULL 6:FULL 7:FULL 8:75-100% free 9:75-100% free 10:FULL 11:25-50% free 12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444d frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444db3 seq: 0x01 flg: 0x00 tail: 0x4db32301 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 13 mapblk 0x00000000 offset: 1 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15687 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 |
第三种情况
[oracle@jumper udump]$ sqlplus "/ as sysdba" SQL*Plus: Release Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release SQL> @a SQL> drop tablespace assm including contents and datafiles; Tablespace dropped. SQL> create tablespace assm 2 datafile '/data1/oracle/oradata/assm01.dbf' 3 size 4 extent management local uniform size 128K 5 segment space management auto 6 / Tablespace created. SQL> SQL> CREATE TABLE TEST_HWM1 (ID CHAR(2000), NAME CHAR(2000) ) 2 Tablespace ASSM 3 STORAGE ( MINEXTENTS 2) PCTFREE 40 4 / Table created. SQL> SQL> SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents 3 where segment_name='TEST_HWM1' 4 / EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 7 7 9 16 1 7 7 25 16 SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> SQL> insert into test_hwm1 values('1','eeyglee'); 1 row created. SQL> commit; Commit complete. SQL> SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> ! [oracle@jumper udump]$ ls a.sql hsjf_ora_14222.trc hsjf_ora_16983.trc hsjf_ora_18039.trc b.sql hsjf_ora_16003.trc hsjf_ora_17568.trc hsjf_ora_18807.trc [oracle@jumper udump]$ ls -sort total 96 16 -rw-r----- 1 oracle 15744 6月 15 11:13 hsjf_ora_14222.trc 12 -rw-r----- 1 oracle 11888 6月 15 11:14 hsjf_ora_16003.trc 12 -rw-r----- 1 oracle 8325 6月 15 11:22 hsjf_ora_16983.trc 12 -rw-r----- 1 oracle 8326 6月 15 11:26 hsjf_ora_17568.trc 4 -rw-r--r-- 1 oracle 121 6月 15 11:29 b.sql 24 -rw-r----- 1 oracle 23800 6月 15 11:29 hsjf_ora_18039.trc 4 -rw-r--r-- 1 oracle 602 6月 15 11: 12 -rw-r----- 1 oracle 8305 6月 15 11:34 hsjf_ora_18807.trc [oracle@jumper udump]$ more hsjf_ora_18807.trc /opt/oracle/admin/hsjf/udump/hsjf_ora_18807.trc Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release ORACLE_HOME = /opt/oracle/product/ System name: Linux Node name: jumper.hurray.com.cn Release: Version: #1 Wed Sep 4 13:35:50 EDT 2002 Machine: i686 Instance name: hsjf Redo thread mounted by this instance: 1 Oracle process number: 11 Unix process pid: 18807, image: oracle@jumper.hurray.com.cn (TNS V1-V3) *** SESSION ID:(9.116) 2004-06-15 11:34:19.132 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444e3e seq: 0x02 flg: 0x00 tail: 0x4e3e2002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 1 parent dba: 0x unformatted: 13 total: 16 first useful block: 3 owning instance : 1 instance ownership changed at Last successful Search Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295 First free datablock : 3 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x
0:Metadata 1:Metadata 2:Metadata 3:unformatted 4:unformatted 5:unformatted 6:unformatted 7:unformatted 8:unformatted 9:unformatted 10:unformatted 11:unformatted 12:unformatted 13:unformatted 14:unformatted 15:unformatted -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444e frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 2 nfree: 2 ffree: 0 pdba: 0x opcode:1 xid: L1 Ranges : -------------------------------------------------------- 0x 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444e42 seq: 0x01 flg: 0x00 tail: 0x4e422301 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 32 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15688 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444e45 seq: 0x03 flg: 0x00 tail: 0x4e452003 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 1 parent dba: 0x unformatted: 0 total: 16 first useful block: 3 owning instance : 1 instance ownership changed at 06/15/2004 11:34:19 Last successful Search 06/15/2004 11:34:19 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 12
Extent Map Block Offset: 4294967295 First free datablock : 3 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 13 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x --一次格式化13个block,第一个exntets 0:Metadata 1:Metadata 2:Metadata 3:75-100% free 4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free 8:75-100% free 9:75-100% free 10:75-100% free 11:75-100% free 12:75-100% free 13:75-100% free 14:25-50% free 15:75-100% free -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444e frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 2 nfree: 2 ffree: 0 pdba: 0x opcode:1 xid: L1 Ranges : -------------------------------------------------------- 0x 0x
-------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444e45 seq: 0x02 flg: 0x00 tail: 0x4e452302 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 32 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 13 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 13 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x Level 1 BMB for Low HWM block: 0x -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x Last Level 1 BMB: 0x Last Level II BMB: 0x Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 15688 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x 0x
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x Extent 1 : L1 dba: 0x --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x
End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 |
再测试J
[oracle@jumper udump]$ sqlplus "/ as sysdba" SQL*Plus: Release Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release SQL> @a SQL> drop tablespace assm including contents and datafiles; Tablespace dropped. SQL> create tablespace assm 2 datafile '/data1/oracle/oradata/assm01.dbf' 3 size 4 extent management local uniform size 256K 5 segment space management auto 6 / Tablespace created. SQL> SQL> CREATE TABLE TEST_HWM1 (ID CHAR(2000), NAME CHAR(2000) ) 2 Tablespace ASSM 3 STORAGE ( MINEXTENTS 2) PCTFREE 40 4 / Table created. SQL> SQL> SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents 3 where segment_name='TEST_HWM1' 4 / EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 7 7 9 32 1 7 7 41 32 SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> SQL> insert into test_hwm1 values('1','eeyglee'); 1 row created. SQL> commit; Commit complete. SQL> SQL> alter system dump datafile 7 block min 9 block max 11; System altered. SQL> ! [oracle@jumper udump]$ ls -sort total 104 16 -rw-r----- 1 oracle 15744 6月 15 11:13 hsjf_ora_14222.trc 12 -rw-r----- 1 oracle 11888 6月 15 11:14 hsjf_ora_16003.trc 12 -rw-r----- 1 oracle 8325 6月 15 11:22 hsjf_ora_16983.trc 12 -rw-r----- 1 oracle 8326 6月 15 11:26 hsjf_ora_17568.trc 4 -rw-r--r-- 1 oracle 121 6月 15 11:29 b.sql 24 -rw-r----- 1 oracle 23800 6月 15 11:29 hsjf_ora_18039.trc 12 -rw-r----- 1 oracle 8305 6月 15 11:34 hsjf_ora_18807.trc 4 -rw-r--r-- 1 oracle 602 6月 15 11: 8 -rw-r----- 1 oracle 7410 6月 15 11:37 hsjf_ora_19159.trc [oracle@jumper udump]$ more hsjf_ora_19159.trc /opt/oracle/admin/hsjf/udump/hsjf_ora_19159.trc Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release ORACLE_HOME = /opt/oracle/product/ System name: Linux Node name: jumper.hurray.com.cn Release: Version: #1 Wed Sep 4 13:35:50 EDT 2002 Machine: i686 Instance name: hsjf Redo thread mounted by this instance: 1 Oracle process number: 11 Unix process pid: 19159, image: oracle@jumper.hurray.com.cn (TNS V1-V3) *** SESSION ID:(9.118) 2004-06-15 11:37:16.067 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444ea9 seq: 0x02 flg: 0x00 tail: 0x4ea92002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 1 parent dba: 0x unformatted: 12 total: 16 first useful block: 4 owning instance : 1 instance ownership changed at Last successful Search Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295 First free datablock : 4 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x 0:Metadata 1:Metadata 2:Metadata 3:Metadata 4:unformatted 5:unformatted 6:unformatted 7:unformatted 8:unformatted 9:unformatted 10:unformatted 11:unformatted 12:unformatted 13:unformatted 14:unformatted 15:unformatted -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444ea9 seq: 0x02 flg: 0x00 tail: 0x4ea92002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 1 parent dba: 0x unformatted: 16 total: 16 first useful block: 0 owning instance : 1 instance ownership changed at Last successful Search Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295 First free datablock : 0 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x00000000 ext#: 0 blk#: 0 ext size: 0 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 HWM Flag: Not Set -------------------------------------------------------- -------------------------------------------------------- 0x
0:unformatted 1:unformatted 2:unformatted 3:unformatted 4:unformatted 5:unformatted 6:unformatted 7:unformatted 8:unformatted 9:unformatted 10:unformatted 11:unformatted 12:unformatted 13:unformatted 14:unformatted 15:unformatted -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444eac seq: 0x02 flg: 0x00 tail: 0x4eac2102 frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 4 nfree: 4 ffree: 0 pdba: 0x opcode:1 xid: L1 Ranges : -------------------------------------------------------- 0x 0x 0x 0x
-------------------------------------------------------- End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x0000.01444eb2 seq: 0x02 flg: 0x00 tail: 0x4eb22002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 1 parent dba: 0x unformatted: 12 total: 16 first useful block: 4 owning instance : 1 instance ownership changed at Last successful Search Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295 First free datablock : 4 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x00000000 ext#: 0 blk#: 0 ext size: 0 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 HWM Flag: Not Set -------------------------------------------------------- -------------------------------------------------------- 0x
0:Metadata 1:Metadata 2:Metadata 3:Metadata 4:unformatted 5:unformatted 6:unformatted 7:unformatted 8:unformatted 9:unformatted 10:unformatted 11:unformatted 12:unformatted 13:unformatted 14:unformatted 15:unformatted -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444eb2 seq: 0x03 flg: 0x00 tail: 0x4eb22003 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 1 parent dba: 0x unformatted: 0 total: 16 first useful block: 0 owning instance : 1 instance ownership changed at 06/15/2004 11:37:16 Last successful Search 06/15/2004 11:37:16 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 15
Extent Map Block Offset: 4294967295 First free datablock : 0 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 HWM=H29 = 41 #blocks below: 28 HWM= 16 + 16 + 8 + 1= 41 mapblk 0x00000000 offset: 0 注意此HWM下包含了未格式化的block HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x ---这是一次格式化的最大值 0:75-100% free 1:75-100% free 2:75-100% free 3:75-100% free 4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free 8:75-100% free 9:75-100% free 10:75-100% free 11:25-50% free 12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444eb2 seq: 0x01 flg: 0x00 tail: 0x4eb22101 frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 4 nfree: 4 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x 0x 0x 0x
-------------------------------------------------------- End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 继续插入数据 SQL> @b SQL> set echo on SQL> insert into test_hwm1 values('1','eeyglee'); 1 row created. SQL> commit; Commit complete. SQL> SQL> alter system dump datafile 7 block min 9 block max 11; System altered. [oracle@jumper udump]$ more hsjf_ora_20288.trc /opt/oracle/admin/hsjf/udump/hsjf_ora_20288.trc Oracle9i With the Partitioning, OLAP and Oracle Data Mining options JServer Release ORACLE_HOME = /opt/oracle/product/ System name: Linux Node name: jumper.hurray.com.cn Release: Version: #1 Wed Sep 4 13:35:50 EDT 2002 Machine: i686 Instance name: hsjf Redo thread mounted by this instance: 1 Oracle process number: 12 Unix process pid: 20288, image: oracle@jumper.hurray.com.cn (TNS V1-V3) *** SESSION ID:(13.32) 2004-06-15 11:44:38.947 Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x scn: 0x frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 1 parent dba: 0x unformatted: 0 total: 16 first useful block: 4 owning instance : 1 instance ownership changed at 06/15/2004 11:44:37 Last successful Search 06/15/2004 11:44:37 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 11
Extent Map Block Offset: 4294967295 First free datablock : 4 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x00000000 ext#: 0 blk#: 0 ext size: 0 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 HWM Flag: Not Set -------------------------------------------------------- -------------------------------------------------------- 0x
0:Metadata 1:Metadata 2:Metadata 3:Metadata 4:25-50% free 5:75-100% free 6:75-100% free 7:75-100% free 8:75-100% free 9:75-100% free 10:75-100% free 11:75-100% free 12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444eb2 seq: 0x03 flg: 0x00 tail: 0x4eb22003 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 1 parent dba: 0x unformatted: 0 total: 16 first useful block: 0 owning instance : 1 instance ownership changed at 06/15/2004 11:37:16 Last successful Search 06/15/2004 11:37:16 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 15
Extent Map Block Offset: 4294967295 First free datablock : 0 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x #blocks in seg. hdr's freelists: 0 #blocks below: 28 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x
0:75-100% free 1:75-100% free 2:75-100% free 3:75-100% free 4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free 8:75-100% free 9:75-100% free 10:75-100% free 11:25-50% free 12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free -------------------------------------------------------- buffer tsn: 16 rdba: 0x scn: 0x0000.01444eb2 seq: 0x01 flg: 0x00 tail: 0x4eb22101 frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 4 nfree: 4 ffree: 0 pdba: 0x opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x 0x 0x 0x
-------------------------------------------------------- End dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 |
3. 结论
对于 extents <= 16 blocks
HWM 移动遵循:
1----à extent blocks – metadata
2----à extent blocks
对于 extents > 16 blocks
HWM移动遵循:
每次移动32个blocks，但是HWM包含未格式化的block，每次格式化16个block或者16 –metadata blocks
