### 1. Oracle ASSM介紹
```sql
 Oracle ASSM(Automatic Segment Space Managed)使用三級點陣圖來管理資料庫中的
 空間,由於bbed工具不支援查詢Segment Header的物理結構，造成Oracle dba無法深
 入準確的查詢點陣圖結構及變化，本文借鑑bbed的展示方式，使用C語言完全解析
 Oracle ASSM的三級點陣圖結構，希望對各位有所幫助。解析結果如下所示，
```

### 2. 測試資料建立
```sql
create table hsql.test1(t1 char(10),t2 char(10)) tablespace test1;
insert into hsql.test1 values('1','a');
insert into hsql.test1 values('2','aa');
insert into hsql.test1 values('3','aaa');
insert into hsql.test1 values('4','aaaa');
insert into hsql.test1 values('5','aaaaa');
insert into hsql.test1 values('6','aaaaaa');
insert into hsql.test1 values('7','aaaaaaa');
insert into hsql.test1 values('8','aaaaaaaa');
insert into hsql.test1 values('9','aaaaaaaaa');
insert into hsql.test1 values('10','aaaaaaaaaa');
commit;
select * from hsql.test1;
begin
for i in 1..10000 loop
   insert into hsql.test1 values(i,'a'||i);
   commit;
end loop;
end;
/
```

### 3. unload 匯出業務資料
```sql
[oracle@sourcedb xdul]$ ./xdul

xdul: Data Unload for Oracle version 1.1.1

Wechat: xidoublestr
Email: 634025070@qq.com
loading default config.......

load config file 'config.txt' successful
loading default control file ......

ts#	fn	rfn	bsize	blocks	filename
---- ---- ---- ----- -------- --------------------------------------------
0	1	1	8192	97280	/oradata/epmsn/system01.dbf
1	2	2	8192	89600	/oradata/epmsn/sysaux01.dbf
2	3	3	8192	8960	/oradata/epmsn/undotbs01.dbf
4	4	4	8192	640	/oradata/epmsn/users01.dbf
6	5	5	8192	12800	/oradata/epmsn/test1.dbf
load control file 'control.txt' successful
XDUL>list user;
userid	username
------	----------
0	SYS
1	PUBLIC
2	CONNECT
3	RESOURCE
4	DBA
5	SYSTEM
6	SELECT_CATALOG_ROLE
7	EXECUTE_CATALOG_ROLE
8	DELETE_CATALOG_ROLE
9	OUTLN
10	EXP_FULL_DATABASE
11	IMP_FULL_DATABASE
12	LOGSTDBY_ADMINISTRATOR
13	DBFS_ROLE
14	DIP
15	AQ_ADMINISTRATOR_ROLE
16	AQ_USER_ROLE
17	DATAPUMP_EXP_FULL_DATABASE
18	DATAPUMP_IMP_FULL_DATABASE
19	ADM_PARALLEL_EXECUTE_TASK
20	GATHER_SYSTEM_STATISTICS
21	ORACLE_OCM
22	RECOVERY_CATALOG_OWNER
23	SCHEDULER_ADMIN
24	HS_ADMIN_SELECT_ROLE
25	HS_ADMIN_EXECUTE_ROLE
26	HS_ADMIN_ROLE
27	GLOBAL_AQ_USER_ROLE
28	OEM_ADVISOR
29	OEM_MONITOR
30	DBSNMP
31	APPQOSSYS
32	WMSYS
33	WM_ADMIN_ROLE
34	JAVAUSERPRIV
35	JAVAIDPRIV
36	JAVASYSPRIV
37	JAVADEBUGPRIV
38	EJBCLIENT
39	JMXSERVER
40	JAVA_ADMIN
41	JAVA_DEPLOY
42	EXFSYS
43	CTXSYS
44	CTXAPP
45	XDB
46	ANONYMOUS
47	XDBADMIN
48	XDB_SET_INVOKER
49	AUTHENTICATEDUSER
50	XDB_WEBSERVICES
51	XDB_WEBSERVICES_WITH_PUBLIC
52	XDB_WEBSERVICES_OVER_HTTP
53	ORDSYS
54	ORDDATA
55	ORDPLUGINS
56	SI_INFORMTN_SCHEMA
2147483638	XS$NULL
57	MDSYS
58	ORDADMIN
59	OLAP_XS_ADMIN
60	OLAPSYS
61	OLAP_DBA
62	CWM_USER
63	OLAP_USER
64	MDDATA
65	SPATIAL_WFS_ADMIN
66	SPATIAL_WFS_ADMIN_USR
67	WFS_USR_ROLE
68	SPATIAL_CSW_ADMIN
69	SPATIAL_CSW_ADMIN_USR
70	CSW_USR_ROLE
71	SYSMAN
72	MGMT_USER
73	MGMT_VIEW
74	FLOWS_FILES
75	APEX_PUBLIC_USER
76	APEX_ADMINISTRATOR_ROLE
77	APEX_030200
78	OWBSYS
79	OWBSYS_AUDIT
80	OWB$CLIENT
81	OWB_DESIGNCENTER_VIEW
82	OWB_USER
83	SCOTT
84	HSQL
85	_NEXT_USER
XDUL>set user;
input username: HSQL
cur_user: HSQL,cur_userid:84
XDUL>XDUL>
XDUL>list table;
OBJ#,DATAOBJ#,OWNER#,NAME,SUBNAME,ROWCNT,BLKCNT
------------------------------------------------------------
88135,88135,84,TEST1,,10,5
88141,88142,84,TEST_DATE,,11,5
XDUL>desc      
input OBJ#: 88135
Name,Type
----------------------
T1,char(10)
T2,char(10)
XDUL>XDUL>unload table;
input OBJ#: 88135

Unloading table: HSQL.TEST1,object ID: 88135
Unloading segment,storage(Obj#=88135 DataObj#=88135 TS#=6 File#=5 Block#=138 Cluster=0)   --說明段頭塊為 File#=5 Block#=138
10010 rows unloaded
XDUL>

```

### 4. 格式化解析L3 BMB
```sql
XDUL>bmb
input fno: 5
input blk: 138
struct kcbh, 20 bytes			@0  
	ub1 type_kcbh			@0      	  0x23    
	ub1 frmt_kcbh			@1      	  0xa2    
	ub1 spare1_kcbh			@2      	  0x00 
	ub1 spare2_kcbh			@3      	  0x00 
	ub4 rdba_kcbh			@4      	  0x0140008a
	ub4 bas_kcbh			@8      	  0x002508bc
	ub2 wrp_kcbh			@12     	  0x0000
	ub1 seq_kcbh			@14     	  0x03
	ub1 flg_kcbh			@15     	  0x04
	ub2 chkval_kcbh			@16     	  0xf230
	ub2 spare3_kcbh			@18     	  0x0000
struct ech(Extent Control Header)	@36  
	ub4 extents_ech			@36      	  0x00000006
	ub4 blocks_ech			@40      	  0x00000030
	ub4 offset_ech			@44      	  0x00000a9c
	ub4 ext_ech			@48      	  0x00000005
	ub4 blk_ech			@52      	  0x00000008
	ub4 extsize_ech			@56      	  0x00000008
	ub4 hw_ech			@60      	  0x014000b8
	ub4 hwmapblk_ech		@64      	  0x00000000
	ub4 hwoffset_ech		@68      	  0x00000005
	ub4 hwblocksshf_ech		@72      	  0x00000000
	ub4 hwblksbelow_ech		@76      	  0x0000002b
	ub4 lhwmext_ech			@92      	  0x00000005
	ub4 lhwmblk_ech			@96      	  0x00000008
	ub4 lhwmextsize_ech		@100      	  0x00000008
	ub4 lhwm_ech			@104      	  0x014000b8
	ub4 lhwmapblk_ech		@108      	  0x00000000
	ub4 lhwoffset_ech		@112      	  0x00000005
	ub4 lhwblocksshf_ech		@116      	  0x00000000
	ub4 lhwblksbelow_ech		@120      	  0x0000002b
	ub4 segtype_ech			@208      	  0x00000001
	ub4 blksz_ech			@212      	  0x00002000
	ub4 fbsz_echo			@216      	  0x00000000
	ub4 l2asoffset_ech		@220      	  0x00001434
	ub4 firstbmb3_echo		@224      	  0x00000000
	ub4 l2hfinsert_ech		@228      	  0x01400089
	ub4 nl2_echo			@232      	  0x00000001
	ub4 lastBMB1_echo		@236      	  0x014000a8
	ub4 lastBMB2_echo		@240      	  0x01400089
	ub4 lastBMB3_echo		@244      	  0x00000000
	ub4 mhnext_echo			@260      	  0x00000000
	ub4 mhext_echo			@264      	  0x00000006
	ub4 mhobj_ech			@272      	  0x00015847
	ub4 mhflag_ech			@276      	  0x10000000
struct em(Extent Map)			@280  
	ub4 dba_em[0]			@280      	  0x01400088
	ub4 len_em[0]			@284      	  0x00000008
	ub4 dba_em[1]			@288      	  0x01400080
	ub4 len_em[1]			@292      	  0x00000008
	ub4 dba_em[2]			@296      	  0x01400098
	ub4 len_em[2]			@300      	  0x00000008
	ub4 dba_em[3]			@304      	  0x014000a0
	ub4 len_em[3]			@308      	  0x00000008
	ub4 dba_em[4]			@312      	  0x014000a8
	ub4 len_em[4]			@316      	  0x00000008
	ub4 dba_em[5]			@320      	  0x014000b0
	ub4 len_em[5]			@324      	  0x00000008
struct am(Auxillary Map)		@2736  
	ub4 fdba_am[0]			@2736      	  0x01400088
	ub4 datadba_am[0]		@2740      	  0x0140008b
	ub4 fdba_am[1]			@2744      	  0x01400088
	ub4 datadba_am[1]		@2748      	  0x01400080
	ub4 fdba_am[2]			@2752      	  0x01400098
	ub4 datadba_am[2]		@2756      	  0x01400099
	ub4 fdba_am[3]			@2760      	  0x01400098
	ub4 datadba_am[3]		@2764      	  0x014000a0
	ub4 fdba_am[4]			@2768      	  0x014000a8
	ub4 datadba_am[4]		@2772      	  0x014000a9
	ub4 fdba_am[5]			@2776      	  0x014000a8
	ub4 datadba_am[5]		@2780      	  0x014000b0
	ub4 slbbdba1_am			@5192      	  0x01400089
	ub4 tail_kcbh			@8188      	  0x08bc2303
XDUL>
```

### 5. 格式化解析L2 BMB
```sql
XDUL>bmb
input fno: 5
input blk: 137
struct kcbh, 20 bytes			@0  
	ub1 type_kcbh			@0      	  0x21    
	ub1 frmt_kcbh			@1      	  0xa2    
	ub1 spare1_kcbh			@2      	  0x00 
	ub1 spare2_kcbh			@3      	  0x00 
	ub4 rdba_kcbh			@4      	  0x01400089
	ub4 bas_kcbh			@8      	  0x002508ba
	ub2 wrp_kcbh			@12     	  0x0000
	ub1 seq_kcbh			@14     	  0x02
	ub1 flg_kcbh			@15     	  0x04
	ub2 chkval_kcbh			@16     	  0xdebc
	ub2 spare3_kcbh			@18     	  0x0000
struct hc(Header Control)		@76  
	ub4 pdba_hc			@76      	  0x0140008a
	ub4 number_hc			@80      	  0x00000003
	ub4 nfree_hc			@84      	  0x00000001
	ub4 ffree_hc			@88      	  0x00000002
	ub4 inc_hc			@92      	  0x00000000
	ub4 opcode_hc			@96      	  0x00000000
	ub4 objd_hc			@104      	  0x00015847
struct bmb1array			@116  
	ub4 dba_bmb1array[0]		@116      	  0x01400088
	ub4 free_bmb1array[0]		@120      	  0x0001
	ub4 inst_bmb1array[0]		@122      	  0x0001
	ub4 dba_bmb1array[1]		@124      	  0x01400098
	ub4 free_bmb1array[1]		@128      	  0x0001
	ub4 inst_bmb1array[1]		@130      	  0x0001
	ub4 dba_bmb1array[2]		@132      	  0x014000a8
	ub4 free_bmb1array[2]		@136      	  0x0005
	ub4 inst_bmb1array[2]		@138      	  0x0001
	ub4 tail_kcbh			@8188      	  0x08ba2102
XDUL>XDUL>
XDUL>

```

### 6. 格式化解析L1 BMB
```sql
XDUL>
XDUL>bmb
input fno: 5
input blk: 136
struct kcbh, 20 bytes			@0  
	ub1 type_kcbh			@0      	  0x20    
	ub1 frmt_kcbh			@1      	  0xa2    
	ub1 spare1_kcbh			@2      	  0x00 
	ub1 spare2_kcbh			@3      	  0x00 
	ub4 rdba_kcbh			@4      	  0x01400088
	ub4 bas_kcbh			@8      	  0x0024daa8
	ub2 wrp_kcbh			@12     	  0x0000
	ub1 seq_kcbh			@14     	  0x02
	ub1 flg_kcbh			@15     	  0x04
	ub2 chkval_kcbh			@16     	  0xad46
	ub2 spare3_kcbh			@18     	  0x0000
struct hcbmb1(Header Control)		@76  
	ub4 nbits_bmb1			@76      	  0x00000004
	ub4 embo_bmb1			@80      	  0xffffffff
	ub4 fub_bmb1			@88      	  0x00000003
	ub4 total_bmb1			@92      	  0x00000010
	ub2 nranges_bmb1		@96      	  0x0002
	ub2 ownerins_bmb1		@98      	  0x0001
	ub4 nf1_bmb1			@100      	  0x00000000
	ub4 nf2_bmb1			@104      	  0x00000000
	ub4 nf3_bmb1			@108      	  0x00000000
	ub4 nf4_bmb1			@112      	  0x00000000
	ub4 ffd_bmb1			@116      	  0x00000010
	ub4 insowncha_bmb1		@120      	  0x5e875a85
	ub4 lastsucsea_bmb1		@124      	  0x5e875a85
	ub4 pdba_bmb1			@144      	  0x01400089
	ub4 objd_bmb1			@192      	  0x00015847
	ub4 deascnbas_bmb1		@196      	  0x00247325
	ub2 deascnwap_bmb1		@200      	  0x0000
struct dbarange				@116  
	ub4 dba_dbarange[0]		@116      	  0x01400088
	ub4 len_dbarange[0]		@120      	  0x00000008
	ub4 offset_dbarange[0]		@124      	  0x00000000
	ub4 dba_dbarange[1]		@128      	  0x01400080
	ub4 len_dbarange[1]		@132      	  0x00000008
	ub4 offset_dbarange[1]		@136      	  0x00000008
	ub4 tail_kcbh			@8188      	  0xdaa82002
XDUL>
```

### 7. Segment Header Block Types
```
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
```
### 8. dump 段頭塊
```sql
alter session set tracefile_identifier='orastar_01';
oradebug setmypid
alter system dump datafile 5 block 138;
oradebug close_trace
oradebug tracefile_name

--dump Extent Map結果：
Extent Map
  -----------------------------------------------------------------
   0x01400088  length: 8
   0x01400080  length: 8
   0x01400098  length: 8
   0x014000a0  length: 8
   0x014000a8  length: 8
   0x014000b0  length: 8

--手工解析 Extent Map結果：
struct em(Extent Map)			@280  
	ub4 dba_em[0]			@280      	  0x01400088
	ub4 len_em[0]			@284      	  0x00000008
	ub4 dba_em[1]			@288      	  0x01400080
	ub4 len_em[1]			@292      	  0x00000008
	ub4 dba_em[2]			@296      	  0x01400098
	ub4 len_em[2]			@300      	  0x00000008
	ub4 dba_em[3]			@304      	  0x014000a0
	ub4 len_em[3]			@308      	  0x00000008
	ub4 dba_em[4]			@312      	  0x014000a8
	ub4 len_em[4]			@316      	  0x00000008
	ub4 dba_em[5]			@320      	  0x014000b0
	ub4 len_em[5]			@324      	  0x00000008

透過對比Oracle dump結果與手工解析Extent Map結果一致。
```

Oracle assm三級點陣圖結構解析

相關文章