[20150728]关于block record flag1.txt

--昨天被别人问及这个block record flag，google找到如下链接：
http://www.hcdba.com/?p=18
--做一个记录。

#define KDRHFK 0x80 Cluster Key
#define KDRHFC 0x40 Clustered table member
#define KDRHFH 0x20 Head piece of row
#define KDRHFD 0x10 Deleted row
#define KDRHFF 0x08 First data piece
#define KDRHFL 0x04 Last data piece
#define KDRHFP 0x02 First column continues from Previous piece
#define KDRHFN 0x01 Last column continues in Next piece
---------------------------------------------------
AC====>0x80+0x20+0x08+0x04 ===>dump中的(K-H-FL--)
6C====>0x40+0x20+0x08+0x04 ===>dump中的(-CH-FL--)
2C====>0x20+0x08+0x04      ===>dump中的(--H-FL--)
3C====>0x20+0x10+0x08+0x04 ===>dump中的(--HDFL--)

--为了加强记忆，通过一些例子讲解这8个bit。

1.建立测试例子：

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

SCOTT@test> create table t (id number,v1 varchar2(4000),v2 varchar2(4000),v3 varchar2(4000),v4 varchar2(4000));
Table created.

SCOTT@test> insert into t(id) values (1);
1 row created.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> select rowid,t.id from t ;
ROWID                      ID
------------------ ----------
AABLouAAEAAAACjAAA          1

SCOTT@test> @ lookup_rowid AABLouAAEAAAACjAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
    309806          4        163          0 4,163                alter system dump datafile 4 block 163 ;

SCOTT@test> alter system checkpoint;
System altered.

2.通过bbed观察：
BBED> set dba 4,163
        DBA             0x010000a3 (16777379 4,163)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8182     0x2c

BBED> x /rn
rowdata[0]                                  @8182
----------
flag@8182: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8183: 0x01
cols@8184:    1
col    0[2] @8185: 1

--这里的标识是2c。
SCOTT@test> alter system dump datafile 4 block 163 ;
System altered.

block_row_dump:
tab 0, row 0, @0x1f92
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  c1 02
end_of_block_dump

--这基本上最常见的形式。组合起来如下：
#define KDRHFH 0x20 Head piece of row
#define KDRHFF 0x08 First data piece
#define KDRHFL 0x04 Last data piece

3.删除看看：

SCOTT@test> delete from t ;
1 row deleted.

SCOTT@test> alter system checkpoint;
System altered.

--bbed观察，必须退出在进入，否则读到的还是旧的信息。
BBED> set dba 4,163
        DBA             0x010000a3 (16777379 4,163)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8182     0x3c

BBED> x /rn
rowdata[0]                                  @8182
----------
flag@8182: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8183: 0x02
cols@8184:    0

--这里的标识是3c。增加了1个删除的标识。

SCOTT@test> alter system dump datafile 4 block 163 ;
System altered.

block_row_dump:
tab 0, row 0, @0x1f92
tl: 2 fb: --HDFL-- lb: 0x2
end_of_block_dump

--3c,组合起来如下：
#define KDRHFH 0x20 Head piece of row
#define KDRHFD 0x10 Deleted row
#define KDRHFF 0x08 First data piece
#define KDRHFL 0x04 Last data piece

4.rollback看看：
BBED> x /rn
rowdata[0]                                  @8176
----------
flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8177: 0x00
cols@8178:    1

col    0[2] @8179: 1

--如果仔细看地址发生了变化，也就是rollback后而是新写入新的位置。继续修改记录：

SCOTT@test> update t set v1=lpad('a',4000,'a'), v2=lpad('b',4000,'b') where id=1;
1 row updated.

SCOTT@test> commit ;
Commit complete.

BBED> x /rncc
rowdata[0]                                  @4161
----------
flag@4161: 0x28 (KDRHFF, KDRHFH)
lock@4162: 0x02
cols@4163:    2
nrid@4164:0x010000a5.0

col    0[2] @4170: 1
col 1[4000] @4173: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa --太长截断。

--看到的flag标识是0x28，行迁移地址是:
SCOTT@test> set verify off
SCOTT@test> @dfb16 0x010000a5
    RFILE#     BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
         4        165 alter system dump datafile 4 block 165 ;

SCOTT@test> alter system dump datafile 4 block 163 ;
System altered.

--0x28,组合起来如下：
block_row_dump:
tab 0, row 0, @0xfdd
tl: 4015 fb: --H-F--- lb: 0x2  cc: 2
nrid:  0x010000a5.0
col  0: [ 2]  c1 02
col  1: [4000]
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61

#define KDRHFH 0x20 Head piece of row
#define KDRHFF 0x08 First data piece

5.继续观察dba=4,165.

BBED> set dba 4,165
        DBA             0x010000a5 (16777381 4,165)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @4182     0x04

BBED> x /rc
rowdata[0]                                  @4182
----------
flag@4182: 0x04 (KDRHFL)
lock@4183: 0x01
cols@4184:    1

col 0[4000] @4185: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb  --太长截断。

--看到的flag标识是0x04，行迁移地址是:

SCOTT@test> alter system dump datafile 4 block 165 ;
System altered.

block_row_dump:
tab 0, row 0, @0xfda
tl: 4006 fb: -----L-- lb: 0x1  cc: 1
col  0: [4000]
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62

--0x04,组合起来如下：
#define KDRHFL 0x04 Last data piece

6.继续测试：
SCOTT@test> update t set v3=lpad('c',4000,'c'), v4=lpad('d',4000,'d') where id=1;
1 row updated.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> alter system checkpoint;
System altered.

BBED> set dba 4,165
        DBA             0x010000a5 (16777381 4,165)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @170      0x00

BBED> x /rc
rowdata[0]                                  @170
----------
flag@170:  0x00 (NONE)
lock@171:  0x02
cols@172:     1
nrid@173:0x010000a6.0

col 0[4000] @179: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

--flag标识是0x00.

SCOTT@test> @dfb16 0x010000a6
    RFILE#     BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
         4        166 alter system dump datafile 4 block 166 ;

BBED> set dba 4,166
        DBA             0x010000a6 (16777382 4,166)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @4176     0x00

BBED> x /rccc
rowdata[0]                                  @4176
----------
flag@4176: 0x00 (NONE)
lock@4177: 0x01
cols@4178:    1
nrid@4179:0x010000a4.0
col 0[4000] @4185: cccccccccccccccccccccccccccccccccccccccccccccccccc ...

--flag标识是0x00. 行迁移是0x010000a4。
SCOTT@test> @dfb16 0x010000a4
    RFILE#     BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
         4        164 alter system dump datafile 4 block 164 ;

BBED> set dba 4,164
        DBA             0x010000a4 (16777380 4,164)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @4182     0x04

BBED> x /rc
rowdata[0]                                  @4182
----------
flag@4182: 0x04 (KDRHFL)
lock@4183: 0x01
cols@4184:    1

col 0[4000] @4185: dddddddddddddddddddddddddddddddddddddddddddddd

SCOTT@test> alter system dump datafile 4 block 164 ;
System altered.

block_row_dump:
tab 0, row 0, @0xfda
tl: 4006 fb: -----L-- lb: 0x1  cc: 1
col  0: [4000]
64 64 64 64 64 64 64 64 64 64 64 64 64 64 64 64 64 64 64 64 64 64 64 64 64

--没有作出如下两项flag:

#define KDRHFP 0x02 First column continues from Previous piece
#define KDRHFN 0x01 Last column continues in Next piece

--以下有关cluster的，另写一篇blog。
#define KDRHFK 0x80 Cluster Key
#define KDRHFC 0x40 Clustered table member

7.建立2k的数据文件看看：

SCOTT@test> alter system set db_2k_cache_size=5m;
System altered.

CREATE TABLESPACE test2k DATAFILE
  '/u01/app/oracle11g/oradata/test/test2k01.dbf' SIZE 10M AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 2K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SCOTT@test> create table tx  (id number,v1 varchar2(4000),v2 varchar2(4000),v3 varchar2(4000),v4 varchar2(4000)) tablespace test2k;
Table created.

SCOTT@test> insert into tx (id,v1) values (1,lpad('a',4000,'a'));
1 row created.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> alter system checkpoint;
System altered.

SCOTT@test> select rowid,tx.id from tx ;
ROWID                      ID
------------------ ----------
AABLowAAPAAAAIbAAA          1

SCOTT@test> @ lookup_rowid AABLowAAPAAAAIbAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
    309808         15        539          0 15,539               alter system dump datafile 15 block 539

SCOTT@test> alter system dump datafile 15 block 539;
System altered.

--
block_row_dump:
tab 0, row 0, @0x5e3
tl: 437 fb: --H-F--N lb: 0x1  cc: 2
nrid:  0x03c0021a.0
col  0: [ 2]  c1 02
col  1: [422]
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61

--col1 4000个字节，仅仅放了422个字节。出现了分裂。

SCOTT@test> @bbvi 15 539
BVI_COMMAND
------------------------------------------------------------------------------------------
bvi -b 1103872 -s 2048 /u01/app/oracle11g/oradata/test/test2k01.dbf

0010DE30  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 29 .......................)
0010DE48  01 02 03 C0 02 1A 00 00 02 C1 02 FE A6 01 61 61 61 61 61 61 61 61 61 61 ..............aaaaaaaaaa

--flag=0x29. flag包括:
#define KDRHFH 0x20 Head piece of row
#define KDRHFF 0x08 First data piece
#define KDRHFN 0x01 Last column continues in Next piece

SCOTT@test> @dfb16 0x03c0021a
    RFILE#     BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
        15        538 alter system dump datafile 15 block 538 ;

SCOTT@test> alter system dump datafile 15 block 538 ;
System altered.

block_row_dump:
tab 0, row 0, @0x77
tl: 1801 fb: ------PN lb: 0x1  cc: 1
nrid:  0x03c00219.0
col  0: [1789]
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61

--col0仅仅保存了1789，还是没有完成。

SCOTT@test> @bbvi 15 538
BVI_COMMAND
------------------------------------------------------------------------------------------
bvi -b 1101824 -s 2048 /u01/app/oracle11g/oradata/test/test2k01.dbf

0010D0F0  00 00 00 03 01 01 03 C0 02 19 00 00 FE FD 06 61 61 61 61 61 61 61 61 61 ...............aaaaaaaaa
0010D108  61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 aaaaaaaaaaaaaaaaaaaaaaaa

--flag=0x03,flag包括：
#define KDRHFP 0x02 First column continues from Previous piece
#define KDRHFN 0x01 Last column continues in Next piece
--终于看到了这两个标识，使用P，N 表示。

SCOTT@test> @dfb16 0x03c00219
    RFILE#     BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
        15        537 alter system dump datafile 15 block 537 ;

SCOTT@test> alter system dump datafile 15 block 537 ;
System altered.

block_row_dump:
tab 0, row 0, @0x7d
tl: 1795 fb: -----LP- lb: 0x1  cc: 1
col  0: [1789]
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61

SCOTT@test> @bbvi 15 537
BVI_COMMAND
------------------------------------------------------------------------------------------
bvi -b 1099776 -s 2048 /u01/app/oracle11g/oradata/test/test2k01.dbf

0010C8F0  00 00 00 00 00 00 00 00 00 06 01 01 FE FD 06 61 61 61 61 61 61 61 61 61 ...............aaaaaaaaa
0010C908  61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 aaaaaaaaaaaaaaaaaaaaaaaa

--flag=0x06,flag包括：
#define KDRHFL 0x04 Last data piece
#define KDRHFP 0x02 First column continues from Previous piece

--终于over。最后2个表示在8k的数据块应该很难看到，不过问一下别人8i,9i好像看到过。