[20150224]ITL不足的测试例子.txt
--前几天看Apress.Oracle.Database.Transactions.and.Locking.Revealed.1484207610.pdf，发现P39页的测试例子，感觉不对，重复做
--一次。
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t
( x int primary key,
y varchar2(4000)
)
insert into t (x,y) select rownum, rpad('*',148,'*') from dual connect by level commit ;
select length(y),
dbms_rowid.rowid_block_number(rowid) blk,
count(*), min(x), max(x)
from t
group by length(y), dbms_rowid.rowid_block_number(rowid);
LENGTH(Y) BLK COUNT(*) MIN(X) MAX(X)
---------- ---------- ---------- ---------- ----------
148 163 46 1 46
--可以发现记录在同一块中。
SCOTT@test> SCOTT@test> select rowid from t where rownum=1;
ROWID
------------------
AABJJ4AAEAAAACjAAA
SCOTT@test> @lookup_rowid AABJJ4AAEAAAACjAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
299640 4 163 0 4,163 alter system dump datafile 4 block 163 ;
--建立测试过程：
create or replace procedure do_update( p_n in number )
as
pragma autonomous_transaction;
l_rec t%rowtype;
resource_busy exception;
pragma exception_init( resource_busy, -54 );
begin
select * into l_rec from t where x = p_n for update NOWAIT;
do_update( p_n+1 );
commit;
exception
when resource_busy
then
dbms_output.put_line( 'locked out trying to select row ' || p_n );
commit;
when no_data_found
then
dbms_output.put_line( 'we finished - no problems' );
commit;
end;
/
--说明这个是一个递归的过程，每次调用后都会执行1次commit。
SCOTT@test> set serveroutput on
SCOTT@test> exec do_update(1);
locked out trying to select row 38
PL/SQL procedure successfully completed.
--可以发现仅仅执行到38行，停止。
SCOTT@test> alter system checkpoint ;
System altered.
SCOTT@test> alter system dump datafile 4 block 163 ;
System altered.
Block header dump: 0x010000a3
Object id on Block? Y
seg/obj: 0x49278 csc: 0x02.cb66236e itc: 37 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000a0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0011.009.000002e0 0x00c001e2.0171.07 --U- 1 fsc 0x0000.cb662399
0x02 0x001e.00b.00000019 0x00c015dd.001e.0b --U- 1 fsc 0x0000.cb66239a
0x03 0x0018.004.0000012f 0x00c0cc01.003f.12 --U- 1 fsc 0x0000.cb662398
0x04 0x001d.016.00000017 0x00c00113.002f.01 --U- 1 fsc 0x0000.cb662397
0x05 0x0016.010.00000179 0x00c0117a.006a.07 --U- 1 fsc 0x0000.cb662396
0x06 0x0008.005.000073be 0x00c006ea.211e.06 --U- 1 fsc 0x0000.cb662395
0x07 0x0022.01d.00000016 0x00c0017a.0023.08 --U- 1 fsc 0x0000.cb662394
0x08 0x001b.004.00000015 0x00c000eb.001c.01 --U- 1 fsc 0x0000.cb662393
0x09 0x002b.00c.00000016 0x00c003ab.0023.09 --U- 1 fsc 0x0000.cb662392
0x0a 0x0020.00e.00000016 0x00c00386.0024.06 --U- 1 fsc 0x0000.cb662391
0x0b 0x0003.01e.000074c3 0x00c06abe.2464.22 --U- 1 fsc 0x0000.cb662390
0x0c 0x0029.006.00000018 0x00c0022a.002f.07 --U- 1 fsc 0x0000.cb66238f
0x0d 0x0023.004.00000016 0x00c0018e.001b.3b --U- 1 fsc 0x0000.cb66238e
0x0e 0x0024.014.00000016 0x00c01cb5.0023.01 --U- 1 fsc 0x0000.cb66238d
0x0f 0x0027.01d.00000016 0x00c0020a.002b.08 --U- 1 fsc 0x0000.cb66238c
0x10 0x0001.016.000060a4 0x00c009e7.215d.0a --U- 1 fsc 0x0000.cb66238b
0x11 0x000d.012.0000069a 0x00c0062b.01fd.21 --U- 1 fsc 0x0000.cb66238a
0x12 0x0028.01b.00000014 0x00c00223.001c.02 --U- 1 fsc 0x0000.cb662389
0x13 0x0012.01c.000002f1 0x00c001f2.01b9.06 --U- 1 fsc 0x0000.cb662388
0x14 0x0009.012.000078a4 0x00c050b8.2301.2c --U- 1 fsc 0x0000.cb662387
0x15 0x002e.019.00000014 0x00c0027a.0010.0c --U- 1 fsc 0x0000.cb662386
0x16 0x0025.012.00000014 0x00c00791.001b.17 --U- 1 fsc 0x0000.cb662385
0x17 0x0005.01d.0000b8dd 0x00c04d09.2e54.05 --U- 1 fsc 0x0000.cb662384
0x18 0x000f.003.0000079f 0x00c02662.0242.2c --U- 1 fsc 0x0000.cb662383
0x19 0x0006.003.00008375 0x00c063a5.247f.08 --U- 1 fsc 0x0000.cb662382
0x1a 0x002d.013.00000015 0x00c0025b.001b.01 --U- 1 fsc 0x0000.cb662381
0x1b 0x0004.01f.0000771b 0x00c04c90.23d8.28 --U- 1 fsc 0x0000.cb662380
0x1c 0x0014.018.0000033e 0x00c00213.0246.15 --U- 1 fsc 0x0000.cb66237f
0x1d 0x0010.008.000005dc 0x00c02e1f.01ce.0d --U- 1 fsc 0x0000.cb66237e
0x1e 0x000c.00d.00002fd3 0x00c005b6.0aee.24 --U- 1 fsc 0x0000.cb66237d
0x1f 0x0017.01f.0000014a 0x00c028ae.0054.19 --U- 1 fsc 0x0000.cb66237c
0x20 0x0019.01d.00000015 0x00c000bf.002f.0c --U- 1 fsc 0x0000.cb66237b
0x21 0x002c.011.00000016 0x00c00252.0023.0b --U- 1 fsc 0x0000.cb66237a
0x22 0x000e.00d.00000608 0x00c00496.0211.01 --U- 1 fsc 0x0000.cb662379
0x23 0x0013.01e.0000030b 0x00c00202.0167.0b --U- 1 fsc 0x0000.cb662378
0x24 0x001c.016.00000015 0x00c0010b.0021.01 --U- 1 fsc 0x0000.cb662376
0x25 0x0021.014.00000015 0x00c020c3.0022.06 --U- 1 fsc 0x0000.cb662375
bdba: 0x010000a3
--可以发现生成37个itl槽，块中剩余空间已经不足了，无法在继续操作，建立itl槽。
--但是后面提到如果插入的数据是：
-- truncate table t;
insert into t (x,y) select rownum, rpad('*',147,'*') from dual connect by level commit ;
-- 书中提示会成功，感觉不对，这样仅仅节约46个字节，一个itl槽占用24个字节，最多仅仅在容纳2个itl，也就是39个itl槽。
-- 接着测试：
SCOTT@test> set serveroutput on
SCOTT@test> exec do_update(1);
locked out trying to select row 40
PL/SQL procedure successfully completed.
--可以发现结果与书中的不同，我在10g下也做了测试，结果是一样的。要想全部执行成功。y长度少1个字节，多2个itl槽。
--这样算下来，至少：46-39=7 , 7/2=3.5 , 147-4=143.仅仅这样可以实现：
SCOTT@test> truncate table t;
Table truncated.
SCOTT@test> insert into t (x,y) select rownum, rpad('*',143,'*') from dual connect by level 46 rows created.
SCOTT@test> commit;
Commit complete.
SCOTT@test> exec do_update(1);
we finished - no problems
PL/SQL procedure successfully completed.
--如果字段y的长度是144，有1条无法处理。
SCOTT@test> truncate table t;
Table truncated.
SCOTT@test> insert into t (x,y) select rownum, rpad('*',144,'*') from dual connect by level 46 rows created.
SCOTT@test> commit;
Commit complete.
SCOTT@test> exec do_update(1);
locked out trying to select row 46
PL/SQL procedure successfully completed.