一、概念介绍
Oracle数据库table cluster 是一组共享公共列并在相同块中存储相关数据的表。当使用 table cluster 时，单个数据块可以包含来自多个表的行。例如，一个块可以存储来自 employees 和 departments 表的行，而不是只存储来自单个表的行。 --from Oracle® DatabaseConcepts
在Oracle数据库中table Cluster表有两种：indexed cluster和hash cluster,两者的区别请参考以下链接学习，
http://blog.itpub.net/31442014/viewspace-2679053/，或查询Oracle官方文档Oracle®Database Concepts章节学习。
本节以system表空间中C_USER#为例,主要使用bbed、dump工具解析indexedcluster结构。
二、数据字典解析
*******C_USER#的cluster_type为: index
SQL> select owner,CLUSTER_NAME,CLUSTER_TYPE from dba_clusterswhere cluster_name='C_USER#';
OWNER CLUSTER_NACLUST
---------- ---------- -----
SYS C_USER# INDEX
SQL>
*******C_USER#的成员表有：USER$、TSQ$
SQL> set linesize 200 pagesize 200
col TAB_COLUMN_NAME for a10
col OWNER for a10
col CLUSTER_NAME for a10
select * from dba_clu_columns where cluster_name='C_USER#';
OWNER CLUSTER_NACLU_COLUMN_NAME TABLE_NAME TAB_COLUMN
---------- ---------- ------------------------------------------------------------ ----------
SYS C_USER# USER# USER$ USER#
SYS C_USER# USER# TSQ$ USER#
SQL>
************C_USER#及其成员表的ddl定义语句如下所示，
set pages 999
set long 9999
select dbms_metadata.get_ddl('CLUSTER','C_USER#') from dual;
select dbms_metadata.get_ddl('TABLE','USER$') from dual;
select dbms_metadata.get_ddl('TABLE','TSQ$') from dual;
SQL> set pages 999
set long 9999
SQL> SQL> selectdbms_metadata.get_ddl('CLUSTER','C_USER#') from dual;
select dbms_metadata.get_ddl('TABLE','USER$') from dual;
select dbms_metadata.get_ddl('TABLE','TSQ$') from dual;
DBMS_METADATA.GET_DDL('CLUSTER','C_USER#')
--------------------------------------------------------------------------------
CREATE CLUSTER"SYS"."C_USER#" (
"USER#"NUMBER )
SIZE 372
PCTFREE 10 PCTUSED 40INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS1 FREELIST GROUPS 1
BUFFER_POOL DEFAULTFLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE"SYSTEM"
PARALLEL (DEGREE 1INSTANCES 1)
SQL>
DBMS_METADATA.GET_DDL('TABLE','USER$')
--------------------------------------------------------------------------------
CREATE TABLE"SYS"."USER$"
( "USER#" NUMBER NOT NULL ENABLE,
"NAME"VARCHAR2(30) NOT NULL ENABLE,
"TYPE#"NUMBER NOT NULL ENABLE,
"PASSWORD" VARCHAR2(30),
"DATATS#" NUMBER NOT NULL ENABLE,
"TEMPTS#" NUMBER NOT NULL ENABLE,
"CTIME"DATE NOT NULL ENABLE,
"PTIME"DATE,
"EXPTIME" DATE,
"LTIME"DATE,
"RESOURCE$" NUMBER NOT NULL ENABLE,
"AUDIT$"VARCHAR2(38),
"DEFROLE" NUMBER NOT NULL ENABLE,
"DEFGRP#" NUMBER,
"DEFGRP_SEQ#" NUMBER,
"ASTATUS" NUMBER DEFAULT 0 NOT NULL ENABLE,
"LCOUNT"NUMBER DEFAULT 0 NOT NULL ENABLE,
"DEFSCHCLASS" VARCHAR2(30),
"EXT_USERNAME" VARCHAR2(4000),
"SPARE1"NUMBER,
"SPARE2"NUMBER,
"SPARE3"NUMBER,
"SPARE4"VARCHAR2(1000),
"SPARE5"VARCHAR2(1000),
"SPARE6"DATE
) CLUSTER"SYS"."C_USER#" ("USER#")
SQL>
DBMS_METADATA.GET_DDL('TABLE','TSQ$')
--------------------------------------------------------------------------------
CREATE TABLE"SYS"."TSQ$"
( "TS#" NUMBER NOT NULL ENABLE,
"USER#"NUMBER NOT NULL ENABLE,
"GRANTOR#" NUMBER NOT NULL ENABLE,
"BLOCKS"NUMBER NOT NULL ENABLE,
"MAXBLOCKS" NUMBER,
"PRIV1"NUMBER NOT NULL ENABLE,
"PRIV2"NUMBER NOT NULL ENABLE,
"PRIV3"NUMBER NOT NULL ENABLE
) CLUSTER"SYS"."C_USER#" ("USER#")
SQL>
*******C_USER#的块结构如下所示：
set linesize 200 pagesize 200
col segment_name for a20
col tablespace_name for a20
select segment_name, segment_type, header_file, header_block,extents, max_extents
from dba_segments
where segment_name ='C_USER#';
SEGMENT_NAME SEGMENT_TYPE HEADER_FILEHEADER_BLOCK EXTENTS MAX_EXTENTS
-------------------- ------------------ ----------- ---------------------- -----------
C_USER# CLUSTER 1 208 1 2147483645
select segment_name, tablespace_name, extent_id, file_id,block_id, blocks from dba_extents where segment_name = 'C_USER#';
SEGMENT_NAME TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
-------------------- -------------------- ---------- -------------------- ----------
C_USER# SYSTEM 0 1 208 8
三、段头块解析
BBED> set dba 1,208
DBA 0x004000d0 (4194512 1,208)
BBED>
BBED> p hwmark_ktech
struct hwmark_ktech, 32 bytes @48
ub4 extno_ktehw @48 0x00000000
ub4 blkno_ktehw @52 0x00000005 –已用5个块
ub4 extsize_ktehw @56 0x00000007 --7个可用块
ub4 blkaddr_ktehw @60 0x004000d6 --高水位地址
ub4 mapblk_ktehw @64 0x00000000
ub4 offset_ktehw @68 0x00000000
ub4 flblks_ktehw @72 0x00000005
ub4 blkcnt_ktehw @76 0x00000005
BBED>
[oracle@sourcedb ~]$ ora_rdba 0x004000d6
*******Welcome to use ora_rdba tool authored by orastar.*******
*******weixin: xidoublestar*******
rdba is: 0x4000d6
datafile# is: 1
datablock is: 214
dump command:alter system dump datafile 1 block 214;
[oracle@sourcedb ~]$
已使用的块分别为：file 1 block: 209 210 211 212 213，合计5个blocks。
四、数据块解析
*******Oracle dump脚本
alter session set tracefile_identifier='orastar_clu_1';
oradebug setmypid
alter system dump datafile 1 block 209;
oradebug close_trace
oradebug tracefile_name
输出结果：
/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_15441_orastar_clu_1.trc
BBED> set dba 1,209
DBA 0x004000d1 (4194513 1,209)
BBED>
*******头部信息
**********bbed
BBED> p kdbh
struct kdbh, 14 bytes @92
ub1 kdbhflag @92 0x00 (NONE)
sb1 kdbhntab @93 2 --2个table
sb2 kdbhnrow @94 43 --43行
sb2 kdbhfrre @96 -1
sb2 kdbhfsbo @98 108
sb2 kdbhfseo @100 4524
sb2 kdbhavsp @102 5520
sb2 kdbhtosp @104 5520
BBED>
**********dump:
flag=--------
ntab=2
nrow=43
frre=-1
fsbo=0x6c
fseo=0x11ac
avsp=0x1590
tosp=0x1590
*******Table directory
**********bbed
BBED> p kdbt
struct kdbt[0], 4 bytes @106
sb2 kdbtoffs @106 0
sb2 kdbtnrow @108 21 --table_1: 21行
struct kdbt[1], 4 bytes @110
sb2 kdbtoffs @110 21
sb2 kdbtnrow @112 22 --table_2: 22行
BBED>
*******Row directory
BBED> p kdbr
sb2 kdbr[0] @114 8074
sb2 kdbr[1] @116 8005
sb2 kdbr[2] @118 7829
sb2 kdbr[3] @120 7740
sb2 kdbr[4] @122 7650
sb2 kdbr[5] @124 7559
sb2 kdbr[6] @126 7473
sb2 kdbr[7] @128 7294
sb2 kdbr[8] @130 7192
sb2 kdbr[9] @132 7089
sb2 kdbr[10] @134 6987
sb2 kdbr[11] @136 6794
sb2 kdbr[12] @138 6694
sb2 kdbr[13] @140 6594
sb2 kdbr[14] @142 6489
sb2 kdbr[15] @144 6397
sb2 kdbr[16] @146 6205
sb2 kdbr[17] @148 6101
sb2 kdbr[18] @150 6006
sb2 kdbr[19] @152 5827
sb2 kdbr[20] @154 5718
sb2 kdbr[21] @156 5936
sb2 kdbr[22] @158 5038
sb2 kdbr[23] @160 7762
sb2 kdbr[24] @162 7672
sb2 kdbr[25] @164 7581
sb2 kdbr[26] @166 7495
sb2 kdbr[27] @168 4524
sb2 kdbr[28] @170 7214
sb2 kdbr[29] @172 7111
sb2 kdbr[30] @174 7009
sb2 kdbr[31] @176 6816
sb2 kdbr[32] @178 6716
sb2 kdbr[33] @180 6616
sb2 kdbr[34] @182 6511
sb2 kdbr[35] @184 6419
sb2 kdbr[36] @186 6227
sb2 kdbr[37] @188 6123
sb2 kdbr[38] @190 6028
sb2 kdbr[39] @192 5849
sb2 kdbr[40] @194 5740
sb2 kdbr[41] @196 5632
sb2 kdbr[42] @198 5547
*******Row data
BBED> p *kdbr[0]
rowdata[3550]
-------------
ub1 rowdata[3550] @8166 0xac
BBED> x rnnnn
rowdata[3550] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
/*
The values for the row flag are:
#define KDRHFK 0x80 Cluster Key --
#define KDRHFC 0x40 Clustered table member
#define KDRHFH 0x20 Head piece of row --
#define KDRHFD 0x10 Deleted row
#define KDRHFF 0x08 First data piece --
#define KDRHFL 0x04 Last data piece --
#define KDRHFP 0x02 First column continues from Previous piece
#define KDRHFN 0x01 Last column continues in Next piece
*/
lock@8167: 0x00
cols@8168: 1 --该行记录的列数
/*
一个集群键中的条目可能比一个块中的条目还多。在这种情况下
将是具有相同键的块的链接。下面的行有链接信息
*/
kref@8169: 1 --Current row count for this key in thisblock
mref@8171: 1 --Committed row count for this key in thisblock
hrid@8173:0x004000d1.0 --ROWID of Previous block for this cluster key
nrid@8179:0x004000d1.0 --ROWID of Next block for this cluster key
col 0[2] @8185: 1
**********dump:
tab 0, row 0, @0x1f8a
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 1 comc: 1 pk: 0x004000d1.0 nk: 0x004000d1.0
col 0: [ 2] c1 02
BBED> p *kdbr[1]
rowdata[3481]
-------------
ub1 rowdata[3481] @8097 0xac
BBED> x rnnnn
rowdata[3481] @8097
-------------
flag@8097: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8098: 0x00
cols@8099: 1
kref@8100: 1
mref@8102: 1
hrid@8104:0x004000d1.1
nrid@8110:0x004000d1.1
col 0[1] @8116: 0
BBED> p *kdbr[20]
rowdata[1194]
-------------
ub1 rowdata[1194] @5810 0xac
BBED> x rnnnn
rowdata[1194] @5810
-------------
flag@5810: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@5811: 0x00
cols@5812: 1
kref@5813: 1
mref@5815: 1
hrid@5817:0x004000d1.14
nrid@5823:0x004000d1.14
col 0[2] @5829: 20
BBED>
BBED> p *kdbr[21]
rowdata[1412]
-------------
ub1 rowdata[1412] @6028 0x20
BBED> x rccccccccccccc
rowdata[1412] @6028
-------------
flag@6028: 0x20 (KDRHFH) --#define KDRHFH 0x20 Head piece of row
lock@6029: 0x02
cols@6030: 0 --0列
nrid@6031:0x004000d5.1 --行数据指向datafile 1 block 213
/*
[oracle@sourcedb ~]$ ora_rdba 0x004000d5
*******Welcome to use ora_rdba tool authored by orastar.*******
*******weixin: xidoublestar*******
rdba is: 0x4000d5
datafile# is: 1
datablock is: 213
dump command:alter system dump datafile 1 block 213;
[oracle@sourcedb ~]$
*/
BBED> p *kdbr[22]
rowdata[514]
------------
ub1 rowdata[514] @5130 0x6c
BBED> x rccccccccc
rowdata[514] @5130
------------
flag@5130: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
/*
The values for the row flag are:
#define KDRHFK 0x80 Cluster Key
#define KDRHFC 0x40 Clustered table member --
#define KDRHFH 0x20 Head piece of row --
#define KDRHFD 0x10 Deleted row
#define KDRHFF 0x08 First data piece --
#define KDRHFL 0x04 Last data piece --
#define KDRHFP 0x02 First column continues from Previous piece
#define KDRHFN 0x01 Last column continues in Next piece
*/
lock@5131: 0x00
cols@5132: 22 --22列
ckix@5133: 1 --cluster key index: 该行属于哪个cluster key
/*
it
is necessary to identify which key a row belongs to. This isdone using the cluster key
index field (cki). The value of cki is a pointer to the rownumber in tab[0]
*/
col 0[3] @5134: SYS
col 1[2] @5138: ..
col 2[16] @5141:466C75A1248EDE33
col 3[1] @5158: .
col 4[2] @5160: ..
col 5[7] @5163:xq...&)
col 6[7] @5171: xx....%
col 7[7] @5179: xq.....
col 8[7] @5187: xq.....
col 9[1] @5195: .
col 10[0] @5197: *NULL*
col 11[2] @5198: ..
col 12[0] @5201: *NULL*
col 13[0] @5202: *NULL*
col 14[1] @5203: .
col 15[1] @5205: .
col 16[22] @5207:DEFAULT_CONSUMER_GROUP
col 17[0] @5230: *NULL*
col 18[1] @5231: .
col 19[0] @5233: *NULL*
col 20[0] @5234: *NULL*
col 21[62] @5235:S:751133058F22AA5809D8578F3C79B9396496CEF838FDBD5C2A570
D10800B
BBED>
BBED> p *kdbr[23]
rowdata[3238]
-------------
ub1 rowdata[3238] @7854 0x6c
BBED> x rcccccccccccc
rowdata[3238] @7854
-------------
flag@7854: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@7855: 0x00
cols@7856: 19
ckix@7857: 0 --ckix=0: 该行隐藏,bbed未显示
/*
BBED> d offset 7857 count 16
File:/oradata/epmsn/system01.dbf (1)
Block: 209 Offsets: 7857 to 7872 Dba:0x004000d1
------------------------------------------------------------------------
00065055 424c49430180ff01 80018007
<32 bytes per line>
BBED>
*/
col 0[6] @7858: PUBLIC
col 1[1] @7865: .
col 2[0] @7867: *NULL*
col 3[1] @7868: .
col 4[1] @7870: .
col 5[7] @7872:xq...&)
col 6[0] @7880: *NULL*
col 7[0] @7881: *NULL*
col 8[0] @7882: *NULL*
col 9[1] @7883: .
col 10[0] @7885: *NULL*
col 11[2] @7886: ..
col 12[0] @7889: *NULL*
col 13[0] @7890: *NULL*
col 14[1] @7891: .
col 15[1] @7893: .
col 16[22] @7895:DEFAULT_CONSUMER_GROUP
col 17[0] @7918: *NULL*
col 18[1] @7919: .
BBED>
BBED> x rccccccccccccc
rowdata[3148] @7764
-------------
flag@7764: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@7765: 0x00
cols@7766: 19
ckix@7767: 2 --非0这里就会显示
col 0[7] @7768: CONNECT
col 1[1] @7776: .
col 2[0] @7778: *NULL*
col 3[1] @7779: .
col 4[1] @7781: .
col 5[7] @7783:xq...&)
col 6[0] @7791: *NULL*
col 7[0] @7792: *NULL*
col 8[0] @7793: *NULL*
col 9[1] @7794: .
col 10[0] @7796: *NULL*
col 11[2] @7797: ..
col 12[0] @7800: *NULL*
col 13[0] @7801: *NULL*
col 14[1] @7802: .
col 15[1] @7804: .
col 16[22] @7806:DEFAULT_CONSUMER_GROUP
col 17[0] @7829: *NULL*
col 18[1] @7830: .
BBED>
五、图解Oracle indexed cluster结构
图1 图解Oracle indexedcluster结构
六、查询user$检查
SQL> select user#,name from user$;
USER# NAME
---------- ------------------------------
0 SYS --与我们解析结果一致
1 PUBLIC
2 CONNECT
3 RESOURCE
4 DBA
5 SYSTEM
6SELECT_CATALOG_ROLE
7 EXECUTE_CATALOG_ROLE
8DELETE_CATALOG_ROLE
9 OUTLN
10EXP_FULL_DATABASE
11IMP_FULL_DATABASE
12LOGSTDBY_ADMINISTRATOR
13 DBFS_ROLE
14 DIP
15AQ_ADMINISTRATOR_ROLE
16 AQ_USER_ROLE
17DATAPUMP_EXP_FULL_DATABASE
18DATAPUMP_IMP_FULL_DATABASE
19ADM_PARALLEL_EXECUTE_TASK
20GATHER_SYSTEM_STATISTICS
41 JAVA_DEPLOY
21 ORACLE_OCM
22RECOVERY_CATALOG_OWNER
23 SCHEDULER_ADMIN
24 HS_ADMIN_SELECT_ROLE
25HS_ADMIN_EXECUTE_ROLE
26 HS_ADMIN_ROLE
27GLOBAL_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
2147483638 XS$NULL
42 EXFSYS
43 CTXSYS
44 CTXAPP
45 XDB
46 ANONYMOUS
47 XDBADMIN
48 XDB_SET_INVOKER
49AUTHENTICATEDUSER
50 XDB_WEBSERVICES
51XDB_WEBSERVICES_WITH_PUBLIC
52XDB_WEBSERVICES_OVER_HTTP
61 OLAP_DBA
53 ORDSYS
54 ORDDATA
55 ORDPLUGINS
56SI_INFORMTN_SCHEMA
57 MDSYS
58 ORDADMIN
59 OLAP_XS_ADMIN
60 OLAPSYS
82 OWB_USER
62 CWM_USER
63 OLAP_USER
64 MDDATA
65SPATIAL_WFS_ADMIN
66 SPATIAL_WFS_ADMIN_USR
67 WFS_USR_ROLE
68SPATIAL_CSW_ADMIN
69SPATIAL_CSW_ADMIN_USR
70 CSW_USR_ROLE
71 SYSMAN
72 MGMT_USER
73 MGMT_VIEW
74 FLOWS_FILES
75APEX_PUBLIC_USER
76APEX_ADMINISTRATOR_ROLE
77 APEX_030200
78 OWBSYS
79 OWBSYS_AUDIT
80 OWB$CLIENT
81OWB_DESIGNCENTER_VIEW
86 _NEXT_USER
83 SCOTT
84 HSQL
85 ORASTAR
88 rows selected.
SQL>
七、OS工具解析C_USER#
解析日志：
图2 OS解析user#日志
解析结果：
图3 OS解析user#结果
八、说明
1、以上内容为个人多次测试结果，由于个人原因，如有分析不足之处还请见谅及指正。
2、文章涉及内容，请勿的生产环境模拟。
纸上得来终觉浅，绝知此事要躬行。--陆游
感谢您的阅读，如果您觉得有所收获，也欢迎把文章分享给您的朋友。
长按识别二维码关注我们
