[20180105]oracle临时表补充.txt
--//昨天对临时表做一些测试,今天做一些补充:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create global temporary table t(id number,test varchar2(20),pad varchar2(20)) on commit preserve rows;
SCOTT@book> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
---------------------------- ------- --------------- ---------- ---------- ------- ------------ --- ---------- ---------- ------------ ---------- -----------
/mnt/ramdisk/book/temp01.dbf 1 TEMP 434110464 52992 ONLINE 1 YES 3.4360E+10 4194302 80 433061888 52864
--//临时表文件为/mnt/ramdisk/book/temp01.dbf
2.测试一:
SCOTT@book> insert into t select rownum,'q1w2e3r4','z1x2c3v4' from dual connect by level<=15;
15 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint;
System altered.
$ strings -t x /mnt/ramdisk/book/temp01.dbf | grep q1w2e3r4
--//发出检查点不能将缓存保存的临时数据块脏块内容写盘.只有alter system flush buffer_cache;可以.
SCOTT@book> alter system flush buffer_cache;
System altered.
$ strings -t x /mnt/ramdisk/book/temp01.dbf | grep q1w2e3r4
19b03e9b q1w2e3r4
19b03eb3 q1w2e3r4
19b03ecb q1w2e3r4
19b03ee3 q1w2e3r4
19b03efb q1w2e3r4
19b03f13 q1w2e3r4
19b03f2b q1w2e3r4
19b03f43 q1w2e3r4
19b03f5b q1w2e3r4
19b03f73 q1w2e3r4
19b03f8b q1w2e3r4
19b03fa3 q1w2e3r4
19b03fbb q1w2e3r4
19b03fd3 q1w2e3r4
19b03feb q1w2e3r4
--//OK.
3.测试二:
SCOTT@book> select rowid,t.* from t where rownum<=1;
ROWID ID TEST PAD
------------------ ---------- -------------------- --------------------
AAQM2AAABAAAM2BAAA 1 q1w2e3r4 z1x2c3v4
SCOTT@book> @ &r/rowid AAQM2AAABAAAM2BAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
4246912 1 52609 0 0x40CD81 1,52609 alter system dump datafile 1 block 52609
--//临时表空间文件号从1开始.
SCOTT@book> select * from dba_objects where object_name='T';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ ----------- ---------- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT T 90713 TABLE 2018-01-04 15:42:51 2018-01-04 15:42:51 2018-01-04:15:42:51 VALID Y N N 1
--//临时表仅仅有OBJECT_ID,没有对应的DATA_OBJECT_ID.
SCOTT@book> @ &r/10to16 90713
10 to 16 HEX REVERSE16
-------------- ------------------
0000000016259 0x59620100
--//4246912=0x40cd80
4.做一个转储.
SCOTT@book> alter system dump datafile '/mnt/ramdisk/book/temp01.dbf' block 52609;
alter system dump datafile '/mnt/ramdisk/book/temp01.dbf' block 52609
*
ERROR at line 1:
ORA-01205: not a data file - type number in header is 6
--//不能这样执行.
SCOTT@book> alter system dump tempfile 1 block 52609;
System altered.
SCOTT@book> alter system dump tempfile '/mnt/ramdisk/book/temp01.dbf' block 52609;
System altered.
--//以上2种方式都ok.
Block header dump: 0x0040cd81
Object id on Block? Y
seg/obj: 0x40cd80 csc: 0x03.17618180 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.017.00004e6a 0x00c01064.0eef.0d ---- 15 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0040cd81
data_block_dump,data header at 0x7f967f2cda5c
===============
tsiz: 0x1fa0
hsiz: 0x30
pbl: 0x7f967f2cda5c
76543210
flag=--------
ntab=1
nrow=15
frre=-1
fsbo=0x30
fseo=0x1e38
avsp=0x1e08
tosp=0x1e08
0xe:pti[0] nrow=15 offs=0
0x12:pri[0] offs=0x1e38
0x14:pri[1] offs=0x1e50
0x16:pri[2] offs=0x1e68
0x18:pri[3] offs=0x1e80
0x1a:pri[4] offs=0x1e98
0x1c:pri[5] offs=0x1eb0
0x1e:pri[6] offs=0x1ec8
0x20:pri[7] offs=0x1ee0
0x22:pri[8] offs=0x1ef8
0x24:pri[9] offs=0x1f10
0x26:pri[10] offs=0x1f28
0x28:pri[11] offs=0x1f40
0x2a:pri[12] offs=0x1f58
0x2c:pri[13] offs=0x1f70
0x2e:pri[14] offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1e38
tl: 24 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [ 8] 71 31 77 32 65 33 72 34
col 2: [ 8] 7a 31 78 32 63 33 76 34
tab 0, row 1, @0x1e50
tl: 24 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 03
col 1: [ 8] 71 31 77 32 65 33 72 34
col 2: [ 8] 7a 31 78 32 63 33 76 34
...
tl: 24 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 10
col 1: [ 8] 71 31 77 32 65 33 72 34
col 2: [ 8] 7a 31 78 32 63 33 76 34
end_of_block_dump
End dump data blocks tsn: 3 file#: 1 minblk 52609 maxblk 52609
--//与普通数据块区别不大.通过bbed观察.
BBED> set dba 201,52609
DBA 0x3240cd81 (843107713 201,52609)
BBED> map
File: /mnt/ramdisk/book/temp01.dbf (201)
Block: 52609 Dba:0x3240cd81
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[1], 4 bytes @106
sb2 kdbr[15] @110
ub1 freespace[7688] @140
ub1 rowdata[360] @7828
ub4 tailchk @8188
BBED> x /rnc *kdbr[0]
rowdata[0] @7828
----------
flag@7828: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7829: 0x01
cols@7830: 3
col 0[2] @7831: 1
col 1[8] @7834: q1w2e3r4
col 2[8] @7843: z1x2c3v4
--//有一个小小疑问,oracle如何知道这个块对应的表是临时表T的数据结构.
5.重新再来看看:
SCOTT@book> insert into t select rownum,'q1w2e3r4','z1x2c3v4' from dual connect by level<=5;
5 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select rowid,t.* from t where rownum<=1;
ROWID ID TEST PAD
------------------ ---------- -------------------- --------------------
AAQM2AAABAAAM2BAAA 1 q1w2e3r4 z1x2c3v4
SCOTT@book> @ &r/rowid AAQM2AAABAAAM2BAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
4246912 1 52609 0 0x40CD81 1,52609 alter system dump datafile 1 block 52609
--//发现一个小小的规律:4246912=0x40cd80,而对应的块号是0x40CD81.正好差1.不知道是否巧合.再打开另外会话:
SCOTT@book> insert into t select rownum,'a1b2c3d4','a1s2d3f4' from dual connect by level<=5;
5 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select rowid,t.* from t where rownum<=1;
ROWID ID TEST PAD
------------------ ---------- -------------------- --------------------
AAQM4AAABAAAM4BAAA 1 a1b2c3d4 a1s2d3f4
SCOTT@book> @ &r/rowid AAQM4AAABAAAM4BAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
4247040 1 52737 0 0x40CE01 1,52737 alter system dump datafile 1 block 52737
--//4247040=0x40ce00,也正好差1.而dba=0x40ce00正好对应段头,也就是临时表的段号以段头来命名data_object_id.
SCOTT@book> alter system flush buffer_cache;
System altered.
BBED> set dba 201,52736
DBA 0x3240ce00 (843107840 201,52736)
BBED> map
File: /mnt/ramdisk/book/temp01.dbf (201)
Block: 52736 Dba:0x3240ce00
------------------------------------------------------------
Unlimited Data Segment Header
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
struct kcbh, 20 bytes @0
struct ktech, 72 bytes @20
struct ktemh, 16 bytes @92
struct ktetb[1], 8 bytes @108
struct ktshc, 8 bytes @4148
struct ktsfs_seg[1], 20 bytes @4156
struct ktsfs_txn[16], 320 bytes @4176
ub4 tailchk @8188
6.继续测试:
SCOTT@book> alter system flush buffer_cache;
System altered.
SYS@book> @ &r/bh 1 52609
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
000000008452E458 1 52609 1 data block free 0 0 0 0 0 0 0000000072D6A000
000000008452E458 1 52609 1 data block free 0 0 0 0 0 0 00000000730AA000
000000008452E458 1 52609 1 data block free 0 0 0 0 0 0 0000000077656000
000000008452E458 1 52609 1 data block free 0 0 0 0 0 0 00000000752EC000
--//state=free.
SCOTT@book> select rowid,t.* from t where rownum<=1;
ROWID ID TEST PAD
------------------ ---------- -------------------- --------------------
AAQM2AAABAAAM2BAAA 1 q1w2e3r4 z1x2c3v4
SYS@book> @ &r/bh 1 52609
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
000000008452E458 1 52609 1 data block xcur 1 0 0 0 0 0 0000000072EE4000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000000008452E458 1 52609 1 data block free 0 0 0 0 0 0 0000000072D6A000
000000008452E458 1 52609 1 data block free 0 0 0 0 0 0 00000000730AA000
000000008452E458 1 52609 1 data block free 0 0 0 0 0 0 0000000077656000
000000008452E458 1 52609 1 data block free 0 0 0 0 0 0 00000000752EC000
--//注意看下划线的state=xcur,说明oracle把临时数据块做hash时也是按照dba=1,52609来做的,这样不是和数据文件1(system存在冲突吗)?不知道oracle为什么这样设计.也许很少问题不大.
