[20200814]8K数据库最大行号.txt

来源：这里教程网时间：2026-03-03 16:05:41 作者：

[20200814]8K数据库最大行号.txt --//昨天听别人上课,对方想演示ITL槽不足出现的阻塞和死锁情况,讲到oracle 8K的数据块最大行号不能超过736.实际上 --//比较准确的提法是1块(数据块大小8k)最多仅仅容纳736条记录,实测733.估计采用mssm模式可以达到736条(注:我没测试). --//我记忆里我以前做过一个例子演示行号可以超过这个限制,找了一下以前的测试,在12c上重复演示看看. --//原始链接:http://blog.itpub.net/267265/viewspace-746749/=>[20121019]8k数据块到底能放多少行记录.txt 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 2.建立测试脚本: drop table t2 purge ; SCOTT@test01p> create table t2 (a number) pctfree 0; Table created. 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 28720 28720 T2 --// SPARE1=736. declare v_newrowid rowid; begin for i in 1..4000 loop insert into t2 values(null) returning rowid into v_newrowid; delete from t2 where rowid=v_newrowid; end loop; insert into t2 values(NULL); commit; end; / --//在一个事务里面,插入1条记录占用1个行目录,再删除记录后,由于在一个事务里面,不会重用原来的行目录,这样最终可以确定最大的 --//行号.为了确定表T2占用的块地址,插入1条记录并提交. 3.测试: --//执行以上脚本后: SCOTT@test01p> alter system checkpoint ; System altered. SCOTT@test01p> select rowid,t2.* from t2; ROWID A ------------------ ---------- AAAHAwAALAAAAE2AfB SCOTT@test01p> @ rowid AAAHAwAALAAAAE2AfB OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 28720 11 310 1985 0x2C00136 11,310 alter system dump datafile 11 block 310 --//可以发现行号=1985. SCOTT@test01p> alter table t2 minimize records_per_block; Table altered. 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 ------- ---------- -------------- ----------------- 34753 28720 28720 T2 --// SPARE1=34753. --// 34753-32768=1985,难道12c最大行号1985.具体看后面分析... --//在这样的情况下建立位图索引,不会存在问题. SCOTT@test01p> create bitmap index i_t2_a on t2(a); Index created. SCOTT@test01p> drop index i_t2_a ; Index dropped. --//通过bbed观察,注意bbed for windows查看时block要+1,主要原因是bbed for windows版本对应是9i的,无法识别12c的数据文件的 --//OS头,后面的测试数据块都要+1,不再另外说明. BBED> map dba 11,311 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11) Block: 311 Dba:0x02c00137 ------------------------------------------------------------ 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[1986] @118 ub1 freespace[75] @4090 ub1 rowdata[4023] @4165 ub4 tailchk @8188 --//sb2 kdbr[1986] ,也就是最大行号1985(从0开始计算).如果取消minimize records_per_block设置. SQL> alter table t2 nominimize records_per_block; Table altered. 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 28720 28720 T2 --//spare1=736.还原.再次建立位图索引看看. SCOTT@test01p> create bitmap index i_t2_a on t2(a); create bitmap index i_t2_a on t2(a) * ERROR at line 1: ORA-28604: table too fragmented to build bitmap index (46137654,1985,744) d:\>oerr ora 28604 28604, 00000, "table too fragmented to build bitmap index (%s,%s,%s)" // *Cause: The table has one or more blocks that exceed the maximum number // of rows expected when creating a bitmap index. This is probably // due to deleted rows. The values in the message are: // (data block address, slot number found, maximum slot allowed) // *Action: Defragment the table or block(s). Use the values in the message // to determine the FIRST block affected. (There may be others). --//翻译: 该表有一个或多个块，超过创建位图索引时预期的最大行数。这可能是由于删除了行。 --// 消息中的值是： (数据块地址、找到的插槽号、允许的最大插槽) --//46137654 = set dba 11,310 = alter system dump datefile 11 block 310 = 0x2c00136,对应块地址. --//第1,2个参数都能对上.而第3个参数是允许的最大插槽744,有点奇怪比736大8. 4.继续: --//仔细看了原始链接,发现我现在改写的脚本有一点点问题,难道执行4000次循环.接着 --//插入的行目录在1985.minimize records_per_block具有回收行目录的功能吗? 我记忆当时测试最大2015, --//但是以后重复测试无论如何最大2014.重复测试: drop table t2 purge ; create table t2 (a number) pctfree 0; declare v_newrowid rowid; begin for i in 1..4000 loop insert into t2 values(null) returning rowid into v_newrowid; delete from t2 where rowid=v_newrowid; end loop; end; / insert into t2 values(NULL); commit ; insert into t2 values(NULL); rollback; --//注:这里有点误操作...手快了.... alter system checkpoint ; SCOTT@test01p> select rowid,t2.* from t2; ROWID A ------------------ ---------- AAAHA7AALAAAAE2AfB SCOTT@test01p> @ rowid AAAHA7AALAAAAE2AfB OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 28731 11 310 1985 0x2C00136 11,310 alter system dump datafile 11 block 310 --//注:我当前会话没有退出,应该还是插入在dba=11,310的位置. BBED> map dba 11,311 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11) Block: 311 Dba:0x02c00137 ------------------------------------------------------------ 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[1987] @118 ub1 freespace[70] @4092 ub1 rowdata[4026] @4162 ub4 tailchk @8188 --// sb2 kdbr[1987] @118,说明我当前的行号还可以增加,这样解析不通.这样前面4000-1985 = 2015,无法解析得通. 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 --//噢!注意看下划线,实际上在另外的块行号已经到2015,对于这个回话相当于该块满了,只能选择新的一块继续DML的执行. --//这样可以解析先在dba=11,309插入2015条再删除.再在dba=11,310插入1985条. --//但是还是我无法解析为什么链接http://blog.itpub.net/267265/viewspace-746749/,测试时看到最大2014. declare v_newrowid rowid; begin for i in 1..20000 loop if i=2000 then insert into t2 values(NULL); else insert into t2 values(null) returning rowid into v_newrowid; delete from t2 where rowid=v_newrowid; end if; end loop; end; / --//注以上脚本是我当时执行的脚本.继续观察当前的测试: BBED> set dba 11,310 BBED> p kdbr 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[2006] @4130 4074 sb2 kdbr[2007] @4132 4072 sb2 kdbr[2008] @4134 4070 sb2 kdbr[2009] @4136 4068 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 --//发现1点点奇怪之处,前面的偏移开始相差2,而后面3个相差3.看看dba=11,310的情况: BBED> p dba 11,311 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[1933] @3984 4220 sb2 kdbr[1934] @3986 4218 --//开始相差3. sb2 kdbr[1935] @3988 4215 sb2 kdbr[1936] @3990 4212 sb2 kdbr[1937] @3992 4209 sb2 kdbr[1938] @3994 4206 sb2 kdbr[1939] @3996 4203 sb2 kdbr[1940] @3998 4200 sb2 kdbr[1941] @4000 4197 sb2 kdbr[1942] @4002 4194 sb2 kdbr[1943] @4004 4191 sb2 kdbr[1944] @4006 4188 sb2 kdbr[1945] @4008 4185 sb2 kdbr[1946] @4010 4182 sb2 kdbr[1947] @4012 4179 sb2 kdbr[1948] @4014 4176 sb2 kdbr[1949] @4016 4173 sb2 kdbr[1950] @4018 4170 sb2 kdbr[1951] @4020 4167 sb2 kdbr[1952] @4022 4164 sb2 kdbr[1953] @4024 4161 sb2 kdbr[1954] @4026 4158 sb2 kdbr[1955] @4028 4155 sb2 kdbr[1956] @4030 4152 sb2 kdbr[1957] @4032 4149 sb2 kdbr[1958] @4034 4146 sb2 kdbr[1959] @4036 4143 sb2 kdbr[1960] @4038 4140 sb2 kdbr[1961] @4040 4137 sb2 kdbr[1962] @4042 4134 sb2 kdbr[1963] @4044 4131 sb2 kdbr[1964] @4046 4128 sb2 kdbr[1965] @4048 4125 sb2 kdbr[1966] @4050 4122 sb2 kdbr[1967] @4052 4119 sb2 kdbr[1968] @4054 4116 sb2 kdbr[1969] @4056 4113 sb2 kdbr[1970] @4058 4110 sb2 kdbr[1971] @4060 4107 sb2 kdbr[1972] @4062 4104 sb2 kdbr[1973] @4064 4101 sb2 kdbr[1974] @4066 4098 sb2 kdbr[1975] @4068 4095 sb2 kdbr[1976] @4070 4092 sb2 kdbr[1977] @4072 4089 sb2 kdbr[1978] @4074 4086 sb2 kdbr[1979] @4076 4083 sb2 kdbr[1980] @4078 4080 sb2 kdbr[1981] @4080 4077 sb2 kdbr[1982] @4082 4074 sb2 kdbr[1983] @4084 4071 sb2 kdbr[1984] @4086 4068 sb2 kdbr[1985] @4088 4065 sb2 kdbr[1986] @4090 -1 --//可以确定oracle在dml时做了块内重整.修改了行目录的偏移量.这样可以容纳更多的行号. --//也就是如果循环插入NULL再删除,这样在pctrfee=0的情况下看到的最大行号是2015. 5.看看minimize records_per_block后情况,是否会修改kdbr行目录数量. SCOTT@test01p> alter table t2 minimize records_per_block; Table altered. SCOTT@test01p> alter system checkpoint ; System altered. 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 ------- ---------- -------------- ----------- 34753 28731 28731 T2 --//34753-32767=1986. 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 --//并不会改变行目录数量. BBED> map dba 11,311 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11) Block: 311 Dba:0x02c00137 ------------------------------------------------------------ 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[1987] @118 ub1 freespace[70] @4092 ub1 rowdata[4026] @4162 ub4 tailchk @8188 --//有点乱... 总结: --//1.这种情况在实际的生产环境很难遇到. --//2.从前面建立位图索引时遇到ORA-28604,提示第3个参数是744,是否意味着oracle留有一定的余地.行号在736-744之间时建立位图索 --//引一样建立成功.补充测试看看. ORA-28604: table too fragmented to build bitmap index (46137654,1985,744) d:\>oerr ora 28604 28604, 00000, "table too fragmented to build bitmap index (%s,%s,%s)" // *Cause: The table has one or more blocks that exceed the maximum number // of rows expected when creating a bitmap index. This is probably // due to deleted rows. The values in the message are: // (data block address, slot number found, maximum slot allowed) // *Action: Defragment the table or block(s). Use the values in the message // to determine the FIRST block affected. (There may be others). drop table t2 purge ; create table t2 (a number) pctfree 0; 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 28740 28740 T2 --//SPARE1=736. declare v_newrowid rowid; begin for i in 1..743 loop insert into t2 values(null) returning rowid into v_newrowid; delete from t2 where rowid=v_newrowid; end loop; insert into t2 values(NULL); commit; end; / SCOTT@test01p> select rowid,t2.* from t2; ROWID A ------------------ ---------- AAAHBEAALAAAAE1ALn SCOTT@test01p> @ rowid AAAHBEAALAAAAE1ALn OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 28740 11 309 743 0x2C00135 11,309 alter system dump datafile 11 block 309 SCOTT@test01p> create bitmap index i_t2_a on t2(a); Index created. --//位图索引建立成功,而实际上行号=743. SCOTT@test01p> alter table t2 minimize records_per_block; alter table t2 minimize records_per_block * ERROR at line 1: ORA-28602: statement not permitted on tables containing bitmap indexes SCOTT@test01p> drop index i_t2_a; Index dropped. SCOTT@test01p> alter table t2 minimize records_per_block; Table altered. 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 ------- ---------- -------------- -------------------- 33511 28740 28740 T2 --//33511-32767 = 744. --//如果建立表后执行如下,就无法建立位图索引. declare v_newrowid rowid; begin for i in 1..744 loop insert into t2 values(null) returning rowid into v_newrowid; delete from t2 where rowid=v_newrowid; end loop; insert into t2 values(NULL); commit; end; / --//留给大家测试,我不做了.^_^.

编辑推荐：

返回列表

[20200814]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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推