object table 的存储

一点小研究

SQL> create or replace type studentobj as object(

id number(5),
first_name varchar2(20),
last_name varchar2(20),
major varchar2(20),
current_credits varchar2(20),
member function formattedName
return varchar2,
pragma restrict_references (formattedName,RNDS,WNDS,RNPS,WNPS)
); 成功！

SQL> create or replace type body studentobj as
MEMBER function formattedName
return varchar2
is
begin
return self.first_name|| '.' ||self.last_name;
end;
END;

成功！

SQL> CREATE TABLE T_STUDENT OF studentobj;成功！

SQL> select header_file,header_block from dba_segments ds where ds.segment_name='T_STUDENT';

HEADER_FILE HEADER_BLOCK 12 235

SQL> insert into T_STUDENT values(studentobj(1,'Jack','Smith','aaaaaa',null)); 插入成功

SQL> ALTER SYSTEM DUMP DATAFILE 12 BLOCK 236;

Start dump data blocks tsn: 12 file#: 12 minblk 236 maxblk 236

buffer tsn: 12 rdba: 0x030000ec (12/236)

scn: 0x0000.02307db9 seq: 0x02 flg: 0x04 tail: 0x7db90602

frmt: 0x02 chkval: 0xeaa9 type: 0x06=trans data

Block header dump: 0x030000ec

Object id on Block? Y

seg/obj: 0x774f csc: 0x00.2307db9 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x30000e9 ver: 0x01

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x49d1064

===============

tsiz: 0x1f98

hsiz: 0xe

pbl: 0x049d1064

bdba: 0x030000ec

76543210

flag=--------

ntab=0

nrow=0

frre=-1

fsbo=0xe

fseo=0x1f98

avsp=0x1f8a

tosp=0x1f8a

block_row_dump:

end_of_block_dump

End dump data blocks tsn: 12 file#: 12 minblk 236 maxblk 236

*** 2008-03-10 15:20:50.000

SQL> select * from dba_objects do where do.created >=DATE'2008-03-10'

SQL> SELECT * FROM DBA_SEGMENTS DS WHERE DS.segment_name ='SYS_C003368';
HEADER_FILE HEADER_BLOCK 12 243

SQL> ALTER SYSTEM DUMP DATAFILE 12 BLOCK 244;
Start dump data blocks tsn: 12 file#: 12 minblk 244 maxblk 244

buffer tsn: 12 rdba: 0x030000f4 (12/244)

scn: 0x0000.02397807 seq: 0x01 flg: 0x06 tail: 0x78070601

frmt: 0x02 chkval: 0xe8e4 type: 0x06=trans data

Block header dump: 0x030000f4

Object id on Block? Y

seg/obj: 0x7756 csc: 0x00.2397786 itc: 2 flg: E typ: 2 - INDEX

brn: 0 bdba: 0x30000f1 ver: 0x01

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

0x02 0x0001.028.0000596f 0x00811911.01bc.02 --U- 1 fsc 0x0000.02397807

Leaf block dump

===============

header address 77402212=0x49d1064

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 1

kdxcosdc 0

kdxconro 1

kdxcofbo 38=0x26

kdxcofeo 8011=0x1f4b

kdxcoavs 7973

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 6

kdxlebksz 8036

row#0[8011] flag: -----, lock: 2, data:(6): 03 00 00 ef 00 00

col 0; len 16; (16): 3b 8f e0 ef e8 85 43 b9 8b 1f d9 9c af f5 0b fd

----- end of leaf block dump -----

End dump data blocks tsn: 12 file#: 12 minblk 244 maxblk 244

Rowid :03 00 00 ef 00 00 => datafile 12 block 239

SQL> ALTER system dump datafile 12 block 239;

Start dump data blocks tsn: 12 file#: 12 minblk 239 maxblk 239

buffer tsn: 12 rdba: 0x030000ef (12/239)

scn: 0x0000.02397807 seq: 0x01 flg: 0x06 tail: 0x78070601

frmt: 0x02 chkval: 0xb5d9 type: 0x06=trans data

Block header dump: 0x030000ef

Object id on Block? Y

seg/obj: 0x7755 csc: 0x00.2397805 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x30000e9 ver: 0x01

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0001.028.0000596f 0x00811911.01bc.01 --U- 1 fsc 0x0000.02397807

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x49d1064

===============

tsiz: 0x1f98

hsiz: 0x14

pbl: 0x049d1064

bdba: 0x030000ef

76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x1f6f

avsp=0x1f5b

tosp=0x1f5b

0xe:pti[0] nrow=1 offs=0

0x12:pri[0] offs=0x1f6f

block_row_dump:

tab 0, row 0, @0x1f6f

tl: 41 fb: --H-FL-- lb: 0x1 cc: 5

col 0: [16] 3b 8f e0 ef e8 85 43 b9 8b 1f d9 9c af f5 0b fd

col 1: [ 2] c1 02

col 2: [ 4] 4a 61 63 6b

col 3: [ 5] 53 6d 69 74 68

col 4: [ 6] 61 61 61 61 61 61

end_of_block_dump

End dump data blocks tsn: 12 file#: 12 minblk 239 maxblk 239

col 1: [ 2] c1 02 与 select dump(1,16) from dual 相同，也就是col1 才是studentobj的id 属性。

也就是这里第5个属性current_credits 因为是null ，所以并没有显示，我想这可能是oracle 系统为了节省空间？？

col 0[16] 3b 8f e0 ef e8 85 43 b9 8b 1f d9 9c af f5 0b fd 与SYS_C003368的 键值相同，这个值也就系统分配的内标识。

Trackback URL: http://blog.itpub.net//trackback.php?id=456909