[20200814]8K資料庫最大行號.txt
[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;
/
--//留給大家測試,我不做了.^_^.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2712438/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20211009]8K資料庫最大行號補充.txt資料庫
- 資料庫版本號資料庫
- 更改資料庫埠號資料庫
- oracle資料庫使用sqlldr命令匯入txt資料Oracle資料庫SQL
- [20121019]8k資料塊到底能放多少行記錄.txt
- oracle資料庫調優描述（三）.txtOracle資料庫
- oracle資料庫調優描述（五）.txtOracle資料庫
- oracle資料庫調優描述（二）.txtOracle資料庫
- oracle資料庫調優描述（一）.txtOracle資料庫
- 建立最優的oracle資料庫Oracle資料庫
- 實驗-shell執行資料庫命令.TXT資料庫
- Powerlytics：大資料告訴你美國最賺錢的十大行業–資訊圖大資料行業
- 資料庫的最簡單實現資料庫
- 四大行、城商行等銀行都在使用什麼資料庫？資料庫
- Oracle資料庫SCN號的應用Oracle資料庫
- sqlserver資料庫埠號怎麼修改SQLServer資料庫
- 資料庫查不出資料：傳參不要加引號資料庫
- IP地址資料庫 | 手機號段歸屬地資料庫 | 行政區劃省市區資料庫資料庫
- 最簡單的JAVA連線資料庫Java資料庫
- [20210428]資料庫連線加密.txt資料庫加密
- 1.4.1. 關於Oracle 資料庫版本號Oracle資料庫
- 最接近資料庫的當前SCN號資料庫
- 10.1.0.3資料庫帳號被鎖定資料庫
- 同一資料庫資料SCN號與時間的轉化資料庫
- 為什麼PostgreSQL是最成功的資料庫？SQL資料庫
- [20230223]8k資料塊建立最大檔案是多少(ORA-03206).txt
- [20210529]延遲開啟資料庫.txt資料庫
- [20170515]檢查資料庫scn指令碼.txt資料庫指令碼
- 1.4. 認識你的資料庫版本號資料庫
- 資料庫Patchsets 補丁號碼快速參考資料庫
- 如何建立最簡單的 ABAP 資料庫表，以及編碼從資料庫表中讀取資料 (上)資料庫
- 新手必看！最簡單的MySQL資料庫詳解MySql資料庫
- iOS - 最易用的資料庫工具類 XWDatabase 開源iOS資料庫Database
- 自己實現一個最簡單的資料庫資料庫
- 資料庫容災-沒有最好，只有最適合資料庫
- 最簡便的備份MySql資料庫方法(轉)MySql資料庫
- [20180529]克隆資料庫與dblinks注意.txt資料庫
- [20121214]資料庫錯誤記錄.txt資料庫