本文共 9000 字，大约阅读时间需要 30 分钟。
[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 Productioncreate 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.dbf2.测试一:
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 z1x2c3v4SCOTT@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: 0x01Itl 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 @8188BBED> x /rnc *kdbr[0]
rowdata[0] @7828 ---------- flag@7828: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@7829: 0x01 cols@7830: 3col 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 z1x2c3v4SCOTT@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 a1s2d3f4SCOTT@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 @81886.继续测试:
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 z1x2c3v4SYS@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为什么这样设计.也许很少问题不大.
转载地址：http://olfva.baihongyu.com/