Oracle的存储结构
数据块data block
也叫logical block,是oracle存储数据的最小粒度,一个数据块对应磁盘上数个字节的物理数据库空间。
下图是一个block的结构:
一个block由头部(header),表目录(table directory),行目录(row directory),行数据(row data),空闲空间(free space),事务使用的空间(space used for transaction entries)。
头部包含了块的一些信息:块的地址,段类型(如数据,索引,回滚),一些头部大小为固定的107bytes,块头部的大小是可变的。具体信息如下:
Blocktype
1=undo segment header
2=undo segment block
5=data segment header
7=temporary table
11=data file header
14=unlimited rollback segment header
15=unlimited deferred rollback segment header
16=unlimited data segment header
17=unlimited data segment header with Fl Groups
18=Extent map block
Block format
标识改块的数据库版本,如7,8,9,10,11等
Relative database address
Scn
Scn sequence number
Check value
当db_block_checksum设置之后,用来在块级做完整性检查
Tail
Tail是一个block的末尾,用来作block的一致性检查,它的值等于scn+blocktype+scn sequence number
表目录(table directory)
包含了改块中包含的行的一些信息。用来查找每一行的开始位置(或者行目录),表目录的大小为4 bytes*表的数量。表数量(number of tables)对簇表(cluster table)来说非常重要,而对其他的表来说是1(即表目录的大小是4bytes)。
行目录(row directory)
包含了块中实际行的行信息(如行数据区域每个行片的地址),每行的行目录占用2 bytes。
下图是对oracle行的详细描述(点击图片看大图):
Oracle row包括了row overhead,number of columns,cluster key,rowid,column length
Rowheader
对于非簇表来说,row header 是3bytes,每行拥有一个rowheader,一个字节用来存储标识(flags),一个字节来表示行是否锁定(比如该行updated但是并没有commit),一个字节用来存储列的数量。
Column size
每行的一列都至少需要有1个字节来标示该列的数据的大小,比如说varchar2长度大于250bytes,column size需要3bytes。
Rowid
用来定位行,并不是行的物理位置,但是访问表中一行的最快方式,扩展rowid固定为10bytes,限制(restricted)rowid为6bytes。比如说普通的表的rowid的长度就是10bytes,显示为18位的字符串。
可以看看具体的rowid的值和信息:
SQL> select length(rowid) from dual;
LENGTH(ROWID)
-------------
18
这18位字符串的结构如下:
1-6位代表data object number
7-9位代表file number
10-15位代表block number
16-18位代表row number
可以使用dbms_rowid包内的函数来获得对应的object number,file number,block number和row number:
以下是一些dbms_rowid的过程和函数:
Procedures/Functions
rowid_block_number
function rowid_block_number returns number (
row_id in rowid ,
ts_type_in in varchar2 default
);
rowid_create
function rowid_create returns rowid (
rowid_type in number ,
object_number in number ,
relative_fno in number ,
block_number in number ,
row_number in number
);
rowid_info
procedure rowid_info (
rowid_in in rowid ,
rowid_type out number ,
object_number out number ,
relative_fno out number ,
block_number out number ,
row_number out number ,
ts_type_in in varchar2 default
);
rowid_object
function rowid_object returns number (
row_id in rowid
);
rowid_relative_fno
function rowid_relative_fno returns number (
row_id in rowid ,
ts_type_in in varchar2 default
);
rowid_row_number
function rowid_row_number returns number (
row_id in rowid
);
rowid_to_absolute_fno
function rowid_to_absolute_fno returns number (
row_id in rowid ,
schema_name in varchar2 ,
object_name in varchar2
);
rowid_to_extended
function rowid_to_extended returns rowid (
old_rowid in rowid ,
schema_name in varchar2 ,
object_name in varchar2 ,
conversion_type in number(38)
);
rowid_to_restricted
function rowid_to_restricted returns rowid (
old_rowid in rowid ,
conversion_type in number(38)
);
rowid_type
function rowid_type returns number (
row_id in rowid
);
rowid_verify
function rowid_verify returns number (
rowid_in in rowid ,
schema_name in varchar2 ,
object_name in varchar2 ,
conversion_type in number(38)
);
可以使用上述的一些函数来获得相关信息,比如:
SQL> select rowid,
2 dbms_rowid.rowid_object(rowid) obj_id,
3 dbms_rowid.rowid_relative_fno(rowid) df#,
4 dbms_rowid.rowid_block_number(rowid) blknum,
5 dbms_rowid.rowid_row_number(rowid) rowno
6 from scott.emp where rownum = 1;
ROWID OBJ_ID DF# BLKNUM ROWNO
------------------ ---------- ---------- ---------- ----------
AAAMfPAAEAAAAAeAAA 51151 4 30 0
使用上面的查询结果来看看对应的object信息
SQL> select a.owner, a.object_name, a.object_type
2 from all_objects a
3 where a.object_id = 51151;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SCOTT EMP TABLE
所在数据文件信息:
SQL> select a.tablespace_name, a.file_name
2 from dba_data_files a
3 where a.file_id = 4;
TABLESPACE_NAME FILE_NAME
------------------------------ -------------------------------------------------
USERS E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
可以使用dump函数来分析rowid内的信息:
SQL> select rowid,dump(rowid) from t_test_cusor a where rownum = 1;
ROWID DUMP(ROWID)
------------------ -----------------------------------------------
AAAPZhAAFAAA8RUAAt Typ=69 Len=10: 0,0,246,97,1,67,196,84,0,45
可以使用dump命令来转储前面找到了datafile为4 blocknumber为30的块:
SQL> connect sys/sys@test as sysdba
已连接。
SQL> alter system dump datafile 4 block 30;
系统已更改。
SQL>
以下是dump file的具体信息:
Dump file e:\oracle\product\10.2.0\admin\test\bdump\test_s000_1256.trc
Thu Jun 10 17:32:36 2010
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:967M/1983M, Ph+PgF:2494M/3876M, VA:1302M/2047M
Instance name: test
Redo thread mounted by this instance: 1
Oracle process number: 14
Windows thread id: 1256, image: ORACLE.EXE (S000)
*** 2010-06-10 17:32:36.390
*** ACTION NAME:() 2010-06-10 17:32:36.390
*** MODULE NAME:(sqlplus.exe) 2010-06-10 17:32:36.390
*** SERVICE NAME:(test) 2010-06-10 17:32:36.390
*** SESSION ID:(144.223) 2010-06-10 17:32:36.390
Start dump data blocks tsn: 4 file#: 4 minblk 30 maxblk 30
buffer tsn: 4 rdba: 0x0100001e (4/30)
scn: 0x0000.00227b16 seq: 0x04 flg: 0x04 tail: 0x7b160604
frmt: 0x02 chkval: 0x499f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07718400 to 0x0771A400
7718400 0000A206 0100001E 00227B16 04040000 [.........{".....]
7718410 0000499F 00170001 0000C7CF 00227B0B [.I...........{".]
7718420 1FE80000 00321F02 01000019 00260002 [......2.......&.]
7718430 00000196 0080042D 000B013E 00008000 [....-...>.......]
7718440 000CDB06 00000000 00000000 00000000 [................]
7718450 00000000 00000000 00000000 00000000 [................]
7718460 00000000 000E0100 002E0001 1F461CAB [..............F.]
7718470 00001F46 1CAB000E 00030002 00050004 [F...............]
7718480 00070006 00090008 000B000A 000D000C [................]
7718490 0000FFFF 00000000 00000000 00000000 [................]
77184A0 00000000 00000000 00000000 00000000 [................]
Repeat 453 times
771A100 00000000 00000000 00000000 2C000000 [...............,]
771A110 C1020800 73610302 73610364 02C20364 [......asd.asd...]
771A120 6E780718 0D0D1302 02C20334 04C20218 [..xn....4.......]
771A130 2C15C102 C1020802 6E650502 03656D61 [...,......ename.]
771A140 03647361 071802C2 13026E78 03340D0D [asd.....xn....4.]
771A150 021802C2 C10204C2 08022C15 0302C102 [.........,......]
771A160 03647361 03647361 071802C2 13026E78 [asd.asd.....xn..]
771A170 03340D0D 021802C2 C10204C2 08022C15 [..4..........,..]
771A180 0502C102 6D616E65 73610365 02C20364 [....ename.asd...]
771A190 6E780718 0D0D1302 02C20334 04C20218 [..xn....4.......]
771A1A0 2C15C102 C1020802 73610302 73610364 [...,......asd.as]
771A1B0 02C20364 6E780718 0D0D1302 02C20334 [d.....xn....4...]
771A1C0 04C20218 3C15C102 C2030801 6103464A [.......<....jf.a>
771A1D0 43056161 4B52454C 0350C203 0CB47707 [aa.CLERK..P..w..]
771A1E0 01010111 FF09C202 3C15C102 C2030801 [...........<....>
771A1F0 4105644B 4E454C4C 4C415308 414D5345 [Kd.ALLEN.SALESMA]
771A200 4DC2034E B5770763 01011402 11C20201 [N..Mc.w.........]
771A210 0204C202 013C1FC1 4CC20308 41570416 [......<....l..wa>
771A220 53084452 53454C41 034E414D 07634DC2 [RD.SALESMAN..Mc.]
771A230 1602B577 03010101 02330DC2 C10206C2 [w.........3.....]
771A240 08013C1F 434CC203 4E4F4A05 4D075345 [.<....lc.jones.m>
771A250 47414E41 C2035245 7707284F 010204B5 [ANAGER..O(.w....]
771A260 C2030101 02FF4C1E 013C15C1 4DC20308 [.....L....<....m>
771A270 414D0637 4E495452 4C415308 414D5345 [7.MARTIN.SALESMA]
771A280 4DC2034E B5770763 01011C09 0DC20301 [N..Mc.w.........]
771A290 0FC20233 3C1FC102 C2030801 4205634D [3......<....mc.b>
771A2A0 454B414C 4E414D07 52454741 284FC203 [LAKE.MANAGER..O(]
771A2B0 05B57707 01010101 331DC203 1FC102FF [.w.........3....]
771A2C0 0308013C 05534EC2 52414C43 414D074B [<....ns.clark.ma>
771A2D0 4547414E 4FC20352 B5770728 01010906 [NAGER..O(.w.....]
771A2E0 19C20301 C102FF33 08013C0B 594EC203 [....3....<....ny>
771A2F0 4F435305 41075454 594C414E C2035453 [.SCOTT.ANALYST..]
771A300 7707434C 011304BB C2020101 C102FF1F [LC.w............]
771A310 08013C15 284FC203 4E494B04 52500947 [.<....o>
771A320 44495345 FF544E45 0BB57707 01010111 [ESIDENT..w......]
771A330 FF33C202 3C0BC102 C2030801 54062D4F [..3....<....o-.t>
771A340 454E5255 41530852 4D53454C C2034E41 [URNER.SALESMAN..]
771A350 7707634D 010809B5 C2020101 02800110 [Mc.w............]
771A360 013C1FC1 4FC20308 4441054D 05534D41 [..<....om.adams.>
771A370 52454C43 4EC2034B BB770759 01011705 [CLERK..NY.w.....]
771A380 0CC20201 15C102FF 0208013C 4A0550C2 [........<....p.j>
771A390 53454D41 454C4305 C2034B52 7707634D [AMES.CLERK..Mc.w]
771A3A0 01030CB5 C2030101 02FF330A 013C1FC1 [.........3....<.>
771A3B0 50C20308 4F460403 41074452 594C414E [...P..FORD.ANALY]
771A3C0 C2035453 7707434C 01030CB5 C2020101 [ST..LC.w........]
771A3D0 C102FF1F 08013C15 2350C203 4C494D06 [.....<....p>
771A3E0 0552454C 52454C43 4EC2034B B6770753 [LER.CLERK..NS.w.]
771A3F0 01011701 0EC20201 0BC102FF 7B160604 [...............{]
Block header dump: 0x0100001e
Object id on Block? Y
seg/obj: 0xc7cf csc: 0x00.227b0b itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000019 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.026.00000196 0x0080042d.013e.0b C--- 0 scn 0x0000.000cdb06
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x7718464
===============
tsiz: 0x1f98
hsiz: 0x2e
pbl: 0x07718464
bdba: 0x0100001e
76543210
flag=--------
ntab=1
nrow=14
frre=1
fsbo=0x2e
fseo=0x1cab
avsp=0x1f46
tosp=0x1f46
0xe:pti[0] nrow=14 offs=0
0x12:pri[0] offs=0x1cab
0x14:pri[1] sfll=2
0x16:pri[2] sfll=3
0x18:pri[3] sfll=4
0x1a:pri[4] sfll=5
0x1c:pri[5] sfll=6
0x1e:pri[6] sfll=7
0x20:pri[7] sfll=8
0x22:pri[8] sfll=9
0x24:pri[9] sfll=10
0x26:pri[10] sfll=11
0x28:pri[11] sfll=12
0x2a:pri[12] sfll=13
0x2c:pri[13] sfll=-1
block_row_dump:
tab 0, row 0, @0x1cab
tl: 36 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 2] c1 02
col 1: [ 3] 61 73 64
col 2: [ 3] 61 73 64
col 3: [ 3] c2 02 18
col 4: [ 7] 78 6e 02 13 0d 0d 34
col 5: [ 3] c2 02 18
col 6: [ 2] c2 04
col 7: [ 2] c1 15
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 30 maxblk 30
下面是这个块内一行的具体信息:
SQL> select * from scott.emp where rownum = 1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
1 asd asd 123 2010-2-19 1 123.00 300.00 20
SQL> desc scott.emp
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
结合dump file的内容来分析:
scn: 0x0000.00227b16 seq: 0x04 flg: 0x04 tail: 0x7b160604
这些就是block header的内容了,scn,scn seq,flag以及tail
ntab=1 block内的table数量,为1
nrow=14 block内的总row数,14行
fsbo 空闲空间起始位置
fseo 空闲空间的结束位置
avsp 可用空间
tosp 所有的空间
0xe:pti[0] nrow=14 offs=0 table 0 有14行,开始于行0
0x12:pri[0] offs=0x1cab table 0 中的第一行的位置
0x14:pri[1] sfll=2 可以看到从pri[1]到pri[13]都是sfll=x的标示,这是行目录的内容,说明这些行被清除过。现在这个数据块内实际只有一行有值,即pri[0]。值得注意的是,在block被清除之前,删除掉的行在行目录中仍然拥有offset flag,row entry中的-D- flag标识说明行是否被删除。
下面的:
col 0: [ 2] c1 02
col 1: [ 3] 61 73 64
col 2: [ 3] 61 73 64
col 3: [ 3] c2 02 18
col 4: [ 7] 78 6e 02 13 0d 0d 34
col 5: [ 3] c2 02 18
col 6: [ 2] c2 04
col 7: [ 2] c1 15
则是这一行的8列的具体长度和值。
简单看看其中的第二列和第三列,即:
col 1: [ 3] 61 73 64
col 2: [ 3] 61 73 64
对应的值为:
ENAME JOB
asd asd
其长度和值均相同,长度为3,值分别以16进制数表示,可以用下列sql来具体看看:
SQL> select vsize('asd') sizes,
2 trunc(ascii('a') / 16) || mod(ascii('a'), 16) val,
3 trunc(ascii('s') / 16) || mod(ascii('s'), 16) val,
4 trunc(ascii('d') / 16) || mod(ascii('d'), 16) val
5 from dual;
SIZES VAL VAL VAL
---------- --- --- ---
3 61 73 64
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-664935/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-664935/