[20130325]表Initrans的数量.txt
http://aprakash.wordpress.com/2010/12/18/oracle-tables-initrans/
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
CREATE TABLESPACE TESTMSSM DATAFILE
'/u01/app/oracle11g/oradata/test/testmssm01.dbf' SIZE 64M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
CREATE TABLESPACE TEST DATAFILE
'/u01/app/oracle11g/oradata/test/test01.dbf' SIZE 64M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table t1 tablespace testmssm as select * from dept;
create table t2 tablespace test as select * from dept;
SQL> select table_name,ini_trans from dba_tables where table_name in ('T1','T2') and wner=USER;
TABLE_NAME INI_TRANS
---------- ----------
T2 1
T1 1
--可以发现视图显示Initrans的数量为1.
2.使用bbed查看：
SQL> alter system checkpoint;
System altered.
SQL> select rowid,t1.* from t1 ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABCn5AAJAAAACBAAA 50 TEST aaaaa
AABCn5AAJAAAACBAAB 10 ACCOUNTING NEW YORK
AABCn5AAJAAAACBAAC 20 RESEARCH DALLAS
AABCn5AAJAAAACBAAD 30 SALES CHICAGO
AABCn5AAJAAAACBAAE 40 OPERATIONS BOSTON
AABCn5AAJAAAACBAAF 80 aaaa BBBB
6 rows selected.
SQL> select rowid,t2.* from t2 ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABCn6AAIAAAACTAAA 50 TEST aaaaa
AABCn6AAIAAAACTAAB 10 ACCOUNTING NEW YORK
AABCn6AAIAAAACTAAC 20 RESEARCH DALLAS
AABCn6AAIAAAACTAAD 30 SALES CHICAGO
AABCn6AAIAAAACTAAE 40 OPERATIONS BOSTON
AABCn6AAIAAAACTAAF 80 aaaa BBBB
6 rows selected.
SQL> @lookup_rowid AABCn5AAJAAAACBAAA
OBJECT FILE BLOCK ROW
---------- ---------- ---------- ----------
272889 9 129 0
SQL> @lookup_rowid AABCn6AAIAAAACTAAA
OBJECT FILE BLOCK ROW
---------- ---------- ---------- ----------
272890 8 147 0
BBED> set dba 9,129
DBA 0x02400081 (37748865 9,129)
BBED> p ktbbh.ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0xffff
ub2 kxidslt @46 0x0000
ub4 kxidsqn @48 0x00000000
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00000000
ub2 kubaseq @56 0x0000
ub1 kubarec @58 0x00
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0xc0269096
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0000
ub2 kxidslt @70 0x0000
ub4 kxidsqn @72 0x00000000
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00000000
ub2 kubaseq @80 0x0000
ub1 kubarec @82 0x00
ub2 ktbitflg @84 0x0000 (NONE)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
struct ktbbhitl[2], 24 bytes @92
struct ktbitxid, 8 bytes @92
ub2 kxidusn @92 0x0000
ub2 kxidslt @94 0x0000
ub4 kxidsqn @96 0x00000000
struct ktbituba, 8 bytes @100
ub4 kubadba @100 0x00000000
ub2 kubaseq @104 0x0000
ub1 kubarec @106 0x00
ub2 ktbitflg @108 0x0000 (NONE)
union _ktbitun, 2 bytes @110
sb2 _ktbitfsc @110 0
ub2 _ktbitwrp @110 0x0000
ub4 ktbitbas @112 0x00000000
BBED> set dba 8,147
DBA 0x02000093 (33554579 8,147)
BBED> p ktbbh.ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0xffff
ub2 kxidslt @46 0x0000
ub4 kxidsqn @48 0x00000000
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00000000
ub2 kubaseq @56 0x0000
ub1 kubarec @58 0x00
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0xc02690ac
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0000
ub2 kxidslt @70 0x0000
ub4 kxidsqn @72 0x00000000
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00000000
ub2 kubaseq @80 0x0000
ub1 kubarec @82 0x00
ub2 ktbitflg @84 0x0000 (NONE)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
struct ktbbhitl[2], 24 bytes @92
struct ktbitxid, 8 bytes @92
ub2 kxidusn @92 0x0000
ub2 kxidslt @94 0x0000
ub4 kxidsqn @96 0x00000000
struct ktbituba, 8 bytes @100
ub4 kubadba @100 0x00000000
ub2 kubaseq @104 0x0000
ub1 kubarec @106 0x00
ub2 ktbitflg @108 0x0000 (NONE)
union _ktbitun, 2 bytes @110
sb2 _ktbitfsc @110 0
ub2 _ktbitwrp @110 0x0000
ub4 ktbitbas @112 0x00000000
--可以发现Initrans的数量为3.不管表空间是ASSM或者MSSM。
3.使用转储看看。
SQL> alter system dump datafile 9 block 129;
System altered.
Block header dump: 0x02400081
Object id on Block? Y
seg/obj: 0x429f9 csc: 0x00.c0269096 itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.c0269096
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x02400081
data_block_dump,data header at 0x2a9756e274
--确实缺省Initrans的数量为3个。
时间： 2024-10-01 14:35:33