返回小栈
14_深入解析Oracle table cluster结构
orastar2020-03-23 14:28:11

作者简介

惠星星现就职于北京海天起点,持有OCP 10g、OCP 11g、OCM 11g证书,并有长达10余年电力行业业务维护、数据库维护服务经验,擅长Oracle数据库性能优化、故障处理及Oracle Internal Research。


一、概念介绍



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、文章涉及内容,请勿的生产环境模拟。

 


纸上得来终觉浅,绝知此事要躬行。--陆游



感谢您的阅读,如果您觉得有所收获,也欢迎把文章分享给您的朋友。





0
0