纸上得来终觉浅，绝知此事要躬行
模拟行链接
上一篇 /
下一篇 2008-04-06 18:41:40
/ 个人分类：oracle管理
1.创建测试表T，该表每一行长度8000个字节，由于block大小为8192字节，考虑到pct_free默认设置为10，则每个block将容纳不下每一行数据。
SQL> create table t (a char(2000),b char(2000),c char(2000),d char(2000));
Table created.
SQL> select pct_free,pct_used from user_tables where table_name='T';
PCT_FREE PCT_USED
---------- ----------
10 40
SQL> insert into t select 'a','b','c','d'
2 from sys.dba_objects
3 where rownum<=500;
500 rows created.
2.分析表，并查看该表所占用的block数。SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T');
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks from user_tables where table_name='T';
BLOCKS
----------
1000
可见，500行的数据占用了1000个block。为了更加直观的看到发生了行链接，可以dump数据块
SQL> l
1* select owner,segment_name,file_id,block_id from dba_extents where segment_name='T' and wner='SCOTT' and file_id=2
SQL> a order by 4;
1* select owner,segment_name,file_id,block_id from dba_extents where segment_name='T' and wner='SCOTT' and file_id=2 order by 4
SQL> /
OWNER SEGMENT_NAME FILE_ID BLOCK_ID
------------------------------ -------------------------------------------------- ---------- ----------
SCOTT T 2 18953
SCOTT T 2 18961
SCOTT T 2 18969
SCOTT T 2 18977
SCOTT T 2 18985
SCOTT T 2 18993
SCOTT T 2 19001
SCOTT T 2 19081
SCOTT T 2 19209
SCOTT T 2 19337
SCOTT T 2 19465
OWNER SEGMENT_NAME FILE_ID BLOCK_ID
------------------------------ -------------------------------------------------- ---------- ----------
SCOTT T 2 19593
SCOTT T 2 19721
SCOTT T 2 19849
SCOTT T 2 20721
15 rows selected.
SQL> alter system dump datafile 2 block 18953;
System altered.
查看生成的dump文件：
Start dump data blocks tsn: 3 file#: 2 minblk 18953 maxblk 18953
buffer tsn: 3 rdba: 0x00804a09 (2/18953)
scn: 0x0000.0048b779 seq: 0x01 flg: 0x04 tail: 0xb7790601
frmt: 0x02 chkval: 0x4dce type: 0x06=trans data
Block header dump: 0x00804a09
Object id on Block? Y
seg/obj: 0x1cac csc: 0x00.48b779 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0016.00f.0000000c 0x0140111b.0049.19 C--- 0 scn 0x0000.0048b778
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xaa84e5c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0aa84e5c
bdba: 0x00804a09
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f66
avsp=0x1f52
tosp=0x1f52
0xe:pti[0] nrow=1 ffs=0
0x12:pri[0] ffs=0x1f66
block_row_dump:
tab 0, row 0, @0x1f66
tl: 58 fb: --H-F--N lb: 0x0 cc: 1
nrid: 0x00c004b8.0
col 0: [48]
61 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 3 file#: 2 minblk 18953 maxblk 18953
Dump文件中有红色标出的nrid就是行链接的ROWID信息。
导入论坛
引用链接
收藏
分享给好友
推荐到圈子
管理
举报
TAG:
