[20211009]8K数据库最大行号补充.txt

来源：这里教程网时间：2026-03-03 17:01:49 作者：

[20211009]8K数据库最大行号补充.txt --//别人问的问题不知道是我没讲明白,还是不好理解,里面提到kdbr指向的偏移开始相差2,而后面3个相差3. --//我的意思是oracle在dml时做了块内重整.修改了行目录的偏移量.这样可以容纳更多的行号. --//也就是如果循环插入NULL再删除,这样在pctrfee=0的情况下看到的最大行号是2015. - --//另外的问题,为什么不再继续插入,ub1 freespace[7],还剩下7个字节,按照对方的理解插入1条记录需要5个字节(行目录占2个字节) --//插入null值,rowdate区需占3个字节,实际上不能这样算,oracle为了预留行迁移空间,而保留6个字节给rowid. --//插入1条记录至少需要11字节,行目录2字节+行前面部分3字节+rowid占6字节.这样freespace=7以及块内重整回收3个字节＝10个字节 --//依旧不够的. --//参考链接:http://blog.itpub.net/267265/viewspace-2712438/=>[20200814]8K数据库最大行号.txt BBED> p kdbr sb2 kdbr[0] @118 8086 sb2 kdbr[1] @120 8084 sb2 kdbr[2] @122 8082 sb2 kdbr[3] @124 8080 sb2 kdbr[4] @126 8078 sb2 kdbr[5] @128 8076 sb2 kdbr[6] @130 8074 sb2 kdbr[7] @132 8072 sb2 kdbr[8] @134 8070 sb2 kdbr[9] @136 8068 ... sb2 kdbr[2010] @4138 4066 sb2 kdbr[2011] @4140 4064 sb2 kdbr[2012] @4142 4061 sb2 kdbr[2013] @4144 4058 sb2 kdbr[2014] @4146 4055 --//偏移开始相差2,而后面3个相差3. BBED> map dba 11,310 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11) Block: 310 Dba:0x02c00136 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdbh, 14 bytes @100 struct kdbt[1], 4 bytes @114 sb2 kdbr[2015] @118 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ub1 freespace[7] @4148 ub1 rowdata[4033] @4155 ub4 tailchk @8188 --//可以参考我以前写的 [20150720]为什么8K数据块Hakan Factor=736.txt=>http://blog.itpub.net/267265/viewspace-1742243/ --//一些细节我自己都忘记了,而且这种特例在真实的生产系统根本不可能遇到。重复测试,看看freespace空间的变化过程。 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.建立测试脚本: SCOTT@book> create table t2 (a varchar2(1)) SEGMENT CREATION IMMEDIATE pctfree 0; Table created. --//注:我以前测试使用number,这次测试使用varchar2(1)。 SELECT tab.spare1, obj.object_id, obj.data_object_id, obj.object_name FROM SYS.tab$ tab, dba_objects obj WHERE tab.obj# = obj.object_id AND tab.dataobj# = obj.data_object_id AND obj.owner = USER AND obj.object_name = 'T2'; SPARE1 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME ---------- ---------- -------------- -------------------- 736 93799 93799 T2 $ cat test1.txt select systimestamp from dual; alter system checkpoint; alter system checkpoint; alter system checkpoint; declare v_newrowid rowid; begin for i in 1..1608 loop insert into t2 values(null) returning rowid into v_newrowid; delete from t2 where rowid=v_newrowid; end loop; end; / declare v_newrowid rowid; begin for i in 1..406 loop insert into t2 values(null) returning rowid into v_newrowid; delete from t2 where rowid=v_newrowid; execute immediate 'alter system checkpoint'; sys.dbms_lock.sleep(0.2); end loop; insert into t2 values(NULL); commit; end; / select systimestamp from dual; alter system checkpoint; --//在一个事务里面,插入1条记录占用1个行目录,再删除记录后,由于在一个事务里面,不会重用原来的行目录,这样最终可以确定最大的 --//行号.为了确定表T2占用的块地址,插入1条记录并提交. --//注：我以前测试像以上情况最大行号＝2015,2015-1=2014,循环次数修改为2014最后插入1条，方便定位块地址。 --//打开另外窗口执行如下,里面的dba 4,740可以先注解sleep等内容执行1次来确定.然后删除表重建. --//先执行如下,然后在执行test1.txt脚本. $ seq 900 | xargs -IQ echo "echo map dba 4,740 | bbed parfile=bbed.par cmdfile=cmd.par | grep freespace | ts.awk; sleep 0.2"| bash | tee /tmp/test1.txt 3.测试: SCOTT@book> @ test1.txt SYSTIMESTAMP --------------------------------------------------------------------------- 2021-10-09 09:39:57.101788 +08:00 System altered. System altered. System altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. SYSTIMESTAMP --------------------------------------------------------------------------- 2021-10-09 09:41:20.549225 +08:00 System altered. --//中断窗口2的执行过程. $ vi /tmp/test1.txt [2021-10-09 09:39:57] ub1 freespace[1257] @3444 [2021-10-09 09:39:57] ub1 freespace[25] @3336 [2021-10-09 09:39:57] ub1 freespace[20] @3338 [2021-10-09 09:39:57] ub1 freespace[15] @3340 [2021-10-09 09:39:58] ub1 freespace[10] @3342 [2021-10-09 09:39:58] ub1 freespace[5] @3344 [2021-10-09 09:39:58] ub1 freespace[0] @3346 --//开始插入1608行再删除,发现自由空间迅速从25掉入0,然后回收到1609,可以看出偏移每次下移2个单位.因为行目录增加1行. --//freespace空间每次减少5个单位. [2021-10-09 09:39:58] ub1 freespace[1609] @3348 [2021-10-09 09:39:58] ub1 freespace[1604] @3350 [2021-10-09 09:39:59] ub1 freespace[1599] @3352 [2021-10-09 09:39:59] ub1 freespace[1589] @3356 [2021-10-09 09:39:59] ub1 freespace[1584] @3358 [2021-10-09 09:39:59] ub1 freespace[1579] @3360 ...,... [2021-10-09 09:41:04] ub1 freespace[14] @3986 [2021-10-09 09:41:04] ub1 freespace[9] @3988 [2021-10-09 09:41:04] ub1 freespace[4] @3990 --//再次回收 [2021-10-09 09:41:04] ub1 freespace[321] @3992 [2021-10-09 09:41:04] ub1 freespace[316] @3994 [2021-10-09 09:41:05] ub1 freespace[311] @3996 [2021-10-09 09:41:05] ub1 freespace[306] @3998 [2021-10-09 09:41:05] ub1 freespace[301] @4000 [2021-10-09 09:41:05] ub1 freespace[296] @4002 [2021-10-09 09:41:06] ub1 freespace[286] @4006 [2021-10-09 09:41:06] ub1 freespace[281] @4008 [2021-10-09 09:41:06] ub1 freespace[276] @4010 [2021-10-09 09:41:06] ub1 freespace[271] @4012 [2021-10-09 09:41:06] ub1 freespace[266] @4014 [2021-10-09 09:41:07] ub1 freespace[261] @4016 [2021-10-09 09:41:07] ub1 freespace[256] @4018 [2021-10-09 09:41:07] ub1 freespace[251] @4020 [2021-10-09 09:41:07] ub1 freespace[246] @4022 [2021-10-09 09:41:08] ub1 freespace[241] @4024 [2021-10-09 09:41:08] ub1 freespace[236] @4026 [2021-10-09 09:41:08] ub1 freespace[226] @4030 [2021-10-09 09:41:08] ub1 freespace[221] @4032 [2021-10-09 09:41:08] ub1 freespace[216] @4034 [2021-10-09 09:41:09] ub1 freespace[211] @4036 [2021-10-09 09:41:09] ub1 freespace[206] @4038 [2021-10-09 09:41:09] ub1 freespace[201] @4040 [2021-10-09 09:41:09] ub1 freespace[196] @4042 [2021-10-09 09:41:10] ub1 freespace[191] @4044 [2021-10-09 09:41:10] ub1 freespace[186] @4046 [2021-10-09 09:41:10] ub1 freespace[181] @4048 [2021-10-09 09:41:10] ub1 freespace[176] @4050 [2021-10-09 09:41:10] ub1 freespace[171] @4052 [2021-10-09 09:41:11] ub1 freespace[166] @4054 [2021-10-09 09:41:11] ub1 freespace[161] @4056 [2021-10-09 09:41:11] ub1 freespace[151] @4060 [2021-10-09 09:41:11] ub1 freespace[146] @4062 [2021-10-09 09:41:12] ub1 freespace[141] @4064 [2021-10-09 09:41:12] ub1 freespace[136] @4066 [2021-10-09 09:41:12] ub1 freespace[131] @4068 [2021-10-09 09:41:12] ub1 freespace[126] @4070 [2021-10-09 09:41:12] ub1 freespace[121] @4072 [2021-10-09 09:41:13] ub1 freespace[116] @4074 [2021-10-09 09:41:13] ub1 freespace[111] @4076 [2021-10-09 09:41:13] ub1 freespace[106] @4078 [2021-10-09 09:41:13] ub1 freespace[96] @4082 [2021-10-09 09:41:14] ub1 freespace[91] @4084 [2021-10-09 09:41:14] ub1 freespace[86] @4086 [2021-10-09 09:41:14] ub1 freespace[81] @4088 [2021-10-09 09:41:14] ub1 freespace[76] @4090 [2021-10-09 09:41:14] ub1 freespace[71] @4092 [2021-10-09 09:41:15] ub1 freespace[66] @4094 [2021-10-09 09:41:15] ub1 freespace[61] @4096 [2021-10-09 09:41:15] ub1 freespace[56] @4098 [2021-10-09 09:41:15] ub1 freespace[51] @4100 [2021-10-09 09:41:15] ub1 freespace[46] @4102 [2021-10-09 09:41:16] ub1 freespace[41] @4104 [2021-10-09 09:41:16] ub1 freespace[36] @4106 [2021-10-09 09:41:16] ub1 freespace[31] @4108 [2021-10-09 09:41:16] ub1 freespace[21] @4112 [2021-10-09 09:41:17] ub1 freespace[16] @4114 [2021-10-09 09:41:17] ub1 freespace[11] @4116 [2021-10-09 09:41:17] ub1 freespace[6] @4118 [2021-10-09 09:41:17] ub1 freespace[1] @4120 --//再次回收 [2021-10-09 09:41:18] ub1 freespace[61] @4122 [2021-10-09 09:41:18] ub1 freespace[56] @4124 [2021-10-09 09:41:18] ub1 freespace[51] @4126 [2021-10-09 09:41:18] ub1 freespace[46] @4128 [2021-10-09 09:41:18] ub1 freespace[41] @4130 [2021-10-09 09:41:19] ub1 freespace[31] @4134 [2021-10-09 09:41:19] ub1 freespace[26] @4136 [2021-10-09 09:41:19] ub1 freespace[21] @4138 [2021-10-09 09:41:19] ub1 freespace[16] @4140 [2021-10-09 09:41:19] ub1 freespace[11] @4142 [2021-10-09 09:41:17] ub1 freespace[6] @4118 [2021-10-09 09:41:17] ub1 freespace[1] @4120 [2021-10-09 09:41:18] ub1 freespace[61] @4122 [2021-10-09 09:41:18] ub1 freespace[56] @4124 [2021-10-09 09:41:18] ub1 freespace[51] @4126 [2021-10-09 09:41:18] ub1 freespace[46] @4128 [2021-10-09 09:41:18] ub1 freespace[41] @4130 [2021-10-09 09:41:19] ub1 freespace[31] @4134 [2021-10-09 09:41:19] ub1 freespace[26] @4136 [2021-10-09 09:41:19] ub1 freespace[21] @4138 [2021-10-09 09:41:19] ub1 freespace[16] @4140 [2021-10-09 09:41:19] ub1 freespace[11] @4142 [2021-10-09 09:41:20] ub1 freespace[6] @4144 [2021-10-09 09:41:20] ub1 freespace[1] @4146 --//再次回收 [2021-10-09 09:41:20] ub1 freespace[9] @4148 [2021-10-09 09:41:20] ub1 freespace[9] @4148 [2021-10-09 09:41:21] ub1 freespace[9] @4148 --//不再插入. 4.通过bbed观察: SCOTT@book> alter system checkpoint ; System altered. SCOTT@book> select rowid,t2.* from t2; ROWID A ------------------ - AAAW5nAAEAAAALkAfe SCOTT@book> @ rowid AAAW5nAAEAAAALkAfe OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 93799 4 740 2014 0x10002E4 4,740 alter system dump datafile 4 block 740 ; --//可以发现行号=2014. BBED> set dba 4,740 DBA 0x010002e4 (16777956 4,740) BBED> p kdbr sb2 kdbr[0] @118 8086 sb2 kdbr[1] @120 8084 sb2 kdbr[2] @122 8082 sb2 kdbr[3] @124 8080 sb2 kdbr[4] @126 8078 sb2 kdbr[5] @128 8076 sb2 kdbr[6] @130 8074 ... sb2 kdbr[2008] @4134 4070 sb2 kdbr[2009] @4136 4068 sb2 kdbr[2010] @4138 4066 sb2 kdbr[2011] @4140 4064 sb2 kdbr[2012] @4142 4062 sb2 kdbr[2013] @4144 4060 sb2 kdbr[2014] @4146 4057 --//偏移开始相差2,而后面1个相差3. BBED> map File: /mnt/ramdisk/book/users01.dbf (4) Block: 740 Dba:0x010002e4 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdbh, 14 bytes @100 struct kdbt[1], 4 bytes @114 sb2 kdbr[2015] @118 ub1 freespace[9] @4148 ub1 rowdata[4031] @4157 ub4 tailchk @8188 BBED> x /rx *kdbr[1] rowdata[4027] @8184 ------------- flag@8184: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH) lock@8185: 0x01 cols@8186: 0 ~~~~~~~~~~~~~~~~~~ BBED> x /rx *kdbr[0] rowdata[4029] @8186 ------------- flag@8186: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~` lock@8187: 0x01 cols@8188: 0 --//注意上下输出结合看,实际上offset=8186,前面显示是cols@8186: 0,后面显示的是flag@8186: 0x3c (KDRHFL, KDRHFF, KDRHFD, --//KDRHFH).很明显这个是bbed bug.很明显bbed设计者没有考虑这种非常特殊的情况. BBED> dump offset 8184 count 4 File: /mnt/ramdisk/book/users01.dbf (4) Block: 740 Offsets: 8184 to 8187 Dba:0x010002e4 -------------------------------- 3c013c01 <128 bytes per line> --//明显每个记录仅仅占2个字节. BBED> x /rx *kdbr[2014] rowdata[0] @4157 ---------- flag@4157: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@4158: 0x01 cols@4159: 0 BBED> x /rx *kdbr[2013] rowdata[3] @4160 ---------- flag@4160: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH) lock@4161: 0x01 cols@4162: 0 BBED> dump offset 4157 count 6 File: /mnt/ramdisk/book/users01.dbf (4) Block: 740 Offsets: 4157 to 4162 Dba:0x010002e4 -------------------------------- 2c01003c 013c <128 bytes per line> --//明显每个记录仅仅占3个字节. $ echo -e "set dba 4,740\np kdbr" | rlbbed | tr -d '\r' |grep kdbr | \ > awk 'NR==1{a=$NF;b=0;c=$0} NR>1 {if (a-$NF!=b &&a-$NF!=2) {print c;print $0,a-$NF};b=a-$NF;a=$NF;c=$0}' sb2 kdbr[2013] @4144 4060 sb2 kdbr[2014] @4146 4057 3

编辑推荐：

返回列表

[20211009]8K数据库最大行号补充.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推