[20130325]表Initrans的數量.txt

lfree發表於2013-03-25
[20130325]表Initrans的數量.txt


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個。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-756983/,如需轉載,請註明出處,否則將追究法律責任。

相關文章