SQL> conn xh/a831115
Connected.
SQL> create table t1 (a int,b varchar2(10)) pctfree 0 initrans 1;
Table created.
SQL> SQL> declare
2 begin
for i in 1..1000 loop
3 4 insert into t1 values(i,'a');
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.gather_table_stats('xh','t1');
PL/SQL procedure successfully completed.
SQL> select distinct block# from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from t1);
BLOCK#
----------
3639
3640
SQL> select pct_free,pct_used,ini_trans from user_tables where table_name='T1';
PCT_FREE PCT_USED INI_TRANS
---------- ---------- ----------
0 1
SQL> update t1 set b='aa' where a=1;
1 row updated.
SQL> update t1 set b='aaa' where a=2;
1 row updated.
SQL> select distinct sid from v$mystat;
SID
----------
128
SQL> update t1 set b='aaaa' where a=3;
SQL> select event from v$session where sid=128;
EVENT
---------------------------------------------------------------- TX MODE =4
enq: TX - allocate ITL entry
SQL>
SQL> alter system dump datafile 4 block 3639;
System altered.
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/home/oracle/diag/rdbms/xh/xh/trace/xh_ora_4290.trc
seg/obj: 0x115e6 csc: 0x00.2fadfd itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000e31 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.020.00000339 0x00c00515.0272.6e C--- 0 scn 0x0000.002fad58
0x02 0x0004.00f.000002ec 0x00c013a2.027c.2b ---- 1 fsc 0x0000.00000000
bdba: 0x01000e37
SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('1000e37','xxxxxxxxxx'));
PL/SQL procedure successfully completed.
SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('1000e37','xxxxxxxxx'));
PL/SQL procedure successfully completed.
SQL> print file#
FILE#
----------
4
SQL> print blk#
BLK#
----------
3639
Flag :标记共4位c---表示已经提交,----表示未提交
flag:--u-表示用户已经提交,之后 如果与该块有关的事务再提交 则 这个itl flag变为c---,以前c---的变为----
关于块中itl
SQL> create table t2 (a int, b int);
表已创建。
1 declare
2 begin
3 for i in 1..20 loop
4 insert into t2 values(i,i+1);
5 end loop;
6 commit;
7* end;
SQL> /
PL/SQL 过程已成功完成。
SQL> select distinct block# from (select dbms_rowid.rowid_relative_fno(rowid) fi
le#,dbms_rowid.rowid_block_number(rowid) block# from xh.t2);
BLOCK#
----------
2632
SQL>
SQL> update xh.t2 set b=23 where a=1;
已更新 1 行。
SQL> update xh.t2 set b=24 where a=2;
已更新 1 行。
SQL> update xh.t2 set b=25 where a=3;
已更新 1 行。
SQL> alter system dump datafile 4 block 2632;
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.002.000004b1 0x00800989.04b0.06 ---- 1 fsc 0x0000.00000000
0x02 0x0006.028.00000604 0x00800a20.03fc.26 ---- 1 fsc 0x0000.00000000
0x03 0x0008.00f.000005e5 0x00800093.058f.20 ---- 1 fsc 0x0000.00000000
3个不同的session更新 产生3个 事务 且都未提交oracle会自动扩展itl
SQL> select max_trans from all_tables where table_name='T2';
MAX_TRANS
----------
255
最多可以扩展到255个itl ,可以 扩展但会对性能有影响,可以建表时指定 多个 create table name (id number) initrans 3;
SQL> create table t2 (a int ) initrans 3;
表已创建。
SQL> insert into t2 values(1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select file#,block# from (select dbms_rowid.rowid_relative_fno(rowid) file#
,dbms_rowid.rowid_block_number(rowid) block# from t2);
FILE# BLOCK#
---------- ----------
1 64554
SQL> alter system dump datafile 1 block 64554;
系统已更改。
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.027.000005f5 0x00800aba.0388.34 --U- 1 fsc 0x0000.0053d9b9
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-671717/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-671717/