********************************************************
动力小刚原创文章：
时间： 2013-6-27 22:05:14
原文：http://www.itpub.net/thread-1799520-1-1.html
********************************************************
a. 若无特别说明我的测试环境是database10g
b. 大家可按本文先后顺序复制文中代码进程调试
c. 
如发现错误欢迎立即提出，方便及时更正
d.03/04部分对OS file header和oracle file header，主要是为了理解oracle机制，平时没必要去分析
e. 两种bitmap说明：
ASSM段内的bitmap: 用于管理段内的可空间 （另一篇：http://www.itpub.net/thread-1799192-1-1.html)
LMT文件头内的bitmap: 用于管理该数据文件的可用空间（本文第二部分对管理块3~8的分析）
***********************************************
第01部分-环境准备：创建表空间z1+创建表段zz1
第02部分-块3~8=从表空间位图块查看哪几个区已被段占用
第03部分-补充：块1--OS File Header头的信息
第04部分-补充：块2--Oracle File Header的信息
第01部分-环境准备：创建表空间z1+创建表段zz1
01.1-新建autoextned表空间z1
一、创建可扩展的表空间
SYS@10G> set sqlprompt "_user'@'10G> "
drop tablespace z1 including contents and datafiles cascade constraints;
create tablespace z1 datafile '/tmp/z2.dbf' size 9M autoextend on maxsize 1G, '/tmp/z1.dbf' size 9M;
二、表空间管理方式默认是LMT（EXTENT MANAGEMENT LOCAL）
SYS@10G> set pagesize 0
set long 999
select '--'||dbms_metadata.get_ddl('TABLESPACE', 'Z1') from dual;
--
CREATE TABLESPACE "Z1" DATAFILE
'/tmp/z2.dbf' SIZE 9437184
AUTOEXTEND ON NEXT 8192 MAXSIZE 1073741824,
'/tmp/z1.dbf' SIZE 9437184
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
01.2-建表段zz1并分配extent
一、创建表段zz1
SYS@10G> drop table zz1 purge;
create table zz1 tablespace z1 as select object_name from dba_objects where rownum=1;
二、为zz1分配extent
SYS@10G> begin
for i in 1..2 loop
execute immediate 'alter table zz1 allocate extent';
end loop;
end;
/
三、查看区数目
SYS@10G> set pagesize 0
select '--extents='||count(*) from dba_extents where owner='SYS' and segment_name='ZZ1';
--extents=3
四、查看文件号
SYS@10G> select '--file_id='||file_id from dba_data_files where tablespace_name='Z1';
--file_id=5
--file_id=6
第02部分-块3~8=从表空间位图块查看哪几个区已被段占用
02.1-文件6块3
一、块类型=表空间级的位图块
SYS@10G> set sqlprompt "_user'@'10G> "
conn / as sysdba
alter system dump datafile 6 block 3;
oradebug setmypid
oradebug tracefile_name
--/tmp/orcl_ora_6764.trc
SYS@10G> host grep type /tmp/orcl_ora_6764.trc
frmt: 0x02 chkval: 0x4e62 type: 0x1e=KTFB Bitmapped File Space Bitmap
KTFB Bitmapped File Space Bitmap=表空间级的位图块
二、从表空间级的bitmap分析已分配几个区
SYS@10G> host grep 'BitMap Control:' -A2 /tmp/orcl_ora_6764.trc
BitMap Control:
RelFno: 6, BeginBlock: 9, Flag: 0, First: 3, Free: 63485
0700000000000000 0000000000000000 0000000000000000 0000000000000000
分析方法：BitMap每个byte为一组，组与组之间是从左到右读，但组内是从读往左读
分析过程：07为一个字节=>00000111=>11100000=>表空间中的1,2,3号extent已被分配出去
三、补充：表空间摘要信息
SYS@10G> host grep BeginBlock /tmp/orcl_ora_6764.trc
RelFno: 6, BeginBlock: 9, Flag: 0, First: 3, Free: 63485
1、RelFno: 6,BeginBlock: 9
在10gr2中,块大小=8KB时文件头的前8块是管理块。
2、Flag: 0
0=永久文件，1=临时文件
3、First: 3, Free: 63485
已经分配了3个extent，还有63485是free可以使用的=>猜想：4号块中的bitmap未使用
02.2-文件5块3
一、块类型=表空间级的位图块
SYS@10G> alter system set user_dump_dest='/tmp';
set sqlprompt "_user'@'10G> "
conn / as sysdba
alter system dump datafile 5 block 3;
oradebug setmypid
oradebug tracefile_name
--/tmp/orcl_ora_6776.trc
SYS@10G> host grep type /tmp/orcl_ora_6776.trc
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
type: 0x1e=KTFB Bitmapped File Space Bitmap=表空间级的位图块
补充：块类型小结
Decimal Hex Type
1 0x01 undo segment header
11 0x0b data file header
12 0x0c data segment header with FLG blocks
14 0x0e unlimited undo segment header
15 0x0f unlimited save undo segment header
16 0x10 unlimited data segment header
17 0x11 unlimited data segment header with FLG blocks
18 0x12 extent map block
23 0x17 bitmapped segment header
29 0x1d bitmapped file space header
32 0x20 first level bitmap block
33 0x21 second level bitmap block
34 0x22 third level bitmap block
35 0x23 Pagetable segment header block
36 0x24 Pagetable extent map block
37 0x25 System Managed Undo Extent Map Block
二、从表空间级的bitmap分析已分配几个区
SYS@10G> host grep 'BitMap Control:' -A2 /tmp/orcl_ora_6776.trc
BitMap Control:
RelFno: 5, BeginBlock: 9, Flag: 0, First: 0, Free: 63488
0000000000000000 0000000000000000 0000000000000000 0000000000000000
分析过程：表空间级的区位图全为0说明此文件的区从未被段所占用
三、补充：表空间摘要信息
SYS@10G> host grep BeginBlock /tmp/orcl_ora_6776.trc
RelFno: 5, BeginBlock: 9, Flag: 0, First: 0, Free: 63488
1、RelFno: 5,BeginBlock: 9
在10gr2中BeginBlock: 9,块大小=8KB时文件头的前8块是管理块。
2、Flag: 0
0=永久文件，1=临时文件
3、First: 0, Free: 63488
已经分配了0个extent ，还有63488是free可以使用的
在该表空间未做过删除段操作的情况下块3位图还未使用，推断：4~8位图块bitmap也是空的
第03部分-补充：块1--OS File Header头的信息
此部分信息主要是助于理解oracle的一些机制，日常管理中用处不大。
03.1- DUMP块1
一、alter system dump对OS头是无效的
SYS@10G> conn / as sysdba
alter system dump datafile 5 block 1;
oradebug setmypid
oradebug tracefile_name
-- /tmp/orcl_ora_6776.trc
SYS@10G> host grep --color not /tmp/orcl_ora_6776.trc
Block 1 (file header)not dumped: use dump file header command
二、alter system set events跟踪所有数据文件的块1
SYS@10G> conn / as sysdba
alter system set events 'immediate trace name file_hdrs level 10';
oradebug setmypid
oradebug tracefile_name
-- /tmp/orcl_ora_6780.trc
说明：
Immediate=命令发出后立即将指定的结构dump到跟踪文件中。
level number=从1到10的事件级别号，1=只dump结构头部信息，10=dump结构的所有信息。
03.2-os header：分析表空间有几个数据文件
一、所有表空间的文件列表（prev_file=前一文件号）
SYS@10G> set pagesize 0
host grep -i prev_file /tmp/orcl_ora_6780.trc
tablespace 0, index=1 krfil=1 prev_file=0
tablespace 1, index=2 krfil=2 prev_file=0
tablespace 2, index=3 krfil=3 prev_file=0
tablespace 4, index=4 krfil=4 prev_file=0
tablespace 7, index=6 krfil=5 prev_file=0
tablespace 7, index=6 krfil=6 prev_file=5
tablespace 8, index=8 krfil=7 prev_file=0
tablespace 3, index=5 krfil=1 prev_file=0
SYS@10G> host grep -i prev_file /tmp/orcl_ora_6780.trc|wc -l
--8 共有8个数据文件
二、z1表空间的文件列表
SYS@10G> select '--ts#='||ts# from v$tablespace where name='Z1';
--ts#=7
SYS@10G> host grep -i 'tablespace 7' /tmp/orcl_ora_6780.trc
tablespace 7, index=6 krfil=5 prev_file=0
tablespace 7, index=6 krfil=6 prev_file=5
03.3-os header：其它信息
一、数据文件信息
SYS@10G> host grep -i 'z[12].dbf' -A7 -B1 /tmp/orcl_ora_6780.trc
DATA FILE #5:
(name #9) /tmp/z2.dbf
creation size=1152 block size=8192 status=0xe head=9 tail=9 dup=1
tablespace 7, index=6 krfil=5 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:2 scn: 0x0000.00098d91 04/02/2013 23:23:31
Stop scn: 0xffff.ffffffff 04/02/2013 23:23:30
Creation Checkpointed at scn: 0x0000.00098d8e 04/02/2013 23:23:30
thread:1 rba:(0x5.6342.10)
--
DATA FILE #6:
(name #10) /tmp/z1.dbf
creation size=1152 block size=8192 status=0xe head=10 tail=10 dup=1
tablespace 7, index=6 krfil=6 prev_file=5
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:2 scn: 0x0000.00098d91 04/02/2013 23:23:31
Stop scn: 0xffff.ffffffff 04/02/2013 23:23:31
Creation Checkpointed at scn: 0x0000.00098d90 04/02/2013 23:23:31
thread:1 rba:(0x5.6345.10)
二、表空间信息
SYS@10G> host grep -i "Tablespace #7" -A9 -B0 /tmp/orcl_ora_6780.trc
Tablespace #7 - Z1 rel_fn:5
Creation at scn: 0x0000.00098d8e 04/02/2013 23:23:30
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x305e07df scn: 0x0000.0006ce7b reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2184ef74 scn: 0x0000.00000001 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 01/01/1988 00:00:00
status:0x4 root dba:0x00000000 chkpt cnt: 2 ctl cnt:1
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.00098d91 04/02/2013 23:23:31
thread:1 rba:(0x5.6345.10)
--
Tablespace #7 - Z1 rel_fn:6
Creation at scn: 0x0000.00098d90 04/02/2013 23:23:31
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x305e07df scn: 0x0000.0006ce7b reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2184ef74 scn: 0x0000.00000001 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 01/01/1988 00:00:00
status:0x4 root dba:0x00000000 chkpt cnt: 2 ctl cnt:1
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.00098d91 04/02/2013 23:23:31
thread:1 rba:(0x5.6345.10)
第04部分-补充：块2--Oracle File Header的信息
此部分信息主要是助于理解oracle的一些机制，日常管理中用处不大。
04.1-文件5的块2
一、DUMP文件5块2
SYS@10G> select '--file_id='||file_id from dba_data_files where tablespace_name='Z1';
--file_id=5
--file_id=6
SYS@10G> conn / as sysdba
alter system dump datafile 5 block 2;
oradebug setmypid
oradebug tracefile_name
--/tmp/orcl_ora_6808.trc
二、分析trc文件
SYS@10G> host grep -i head /tmp/orcl_ora_6808.trc
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
File Space Header Block:
Header Control:
Header Opcode:
SYS@10GR2> host grep -i Free -A 1 -B 3 /tmp/orcl_ora_6808.trc
Header Control:
RelFno: 5, Unit: 8, Size: 1152, Flag: 9
AutoExtend: YES, Increment: 1, MaxSize: 131072
Initial Area: 7, Tail: 1152, First: 0, Free: 143
Deallocation scn: 0.0
04.2-DUMP文件6块2
一、DUMP文件6块2
SYS@10G> conn / as sysdba
alter system dump datafile 6 block 2;
oradebug setmypid
oradebug tracefile_name
--/tmp/orcl_ora_6830.trc
二、分析trc文件
SYS@10G> host grep -i head /tmp/orcl_ora_6830.trc
frmt: 0x02 chkval: 0xba00 type: 0x1d=KTFB Bitmapped File Space Header
File Space Header Block:
Header Control:
Header Opcode:
SYS@10GR2>host grep -i Free -A 1 -B 3 /tmp/orcl_ora_6830.trc
Header Control:
RelFno: 6, Unit: 8, Size: 1152, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 1152, First: 3, Free: 140
Deallocation scn: 0.0
04.3-对比文件5和文件6的2号块
SYS@10G> host diff /tmp/orcl_ora_6808.trc /tmp/orcl_ora_6830.trc|grep -Evi ']$|\*\*\*|Dump|TNS|Repeat'
1c1
< /tmp/orcl_ora_6808.trc
---
> /tmp/orcl_ora_6830.trc
13c13
---
15,20c15,20
< buffer tsn: 7 rdba: 0x01400002 (5/2)
< scn: 0x0000.00098d9f seq: 0x01 flg: 0x00 tail: 0x8d9f1d01
< frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
---
> buffer tsn: 7 rdba: 0x01800002 (6/2)
> scn: 0x0000.00098dd6 seq: 0x02 flg: 0x04 tail: 0x8dd61d02
> frmt: 0x02 chkval: 0xba00 type: 0x1d=KTFB Bitmapped File Space Header
22,29c22,31
---
32,34c34,36
< RelFno: 5, Unit: 8, Size: 1152, Flag: 9
< AutoExtend: YES, Increment: 1, MaxSize: 131072
< Initial Area: 7, Tail: 1152, First: 0, Free: 143
---
> RelFno: 6, Unit: 8, Size: 1152, Flag: 1
> AutoExtend: NO, Increment: 0, MaxSize: 0
> Initial Area: 7, Tail: 1152, First: 3, Free: 140
38c40
---
309
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
