[20180105]oracle臨時表補充.txt
[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為什麼這樣設計.也許很少問題不大.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2149725/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle臨時表Oracle
- Oracle 臨時表Oracle
- [20180104]oracle臨時表.txtOracle
- ORACLE臨時表和SQLSERVER臨時表異同OracleSQLServer
- Oracle的臨時表Oracle
- Oracle Temporary Tables(Oracle 臨時表)Oracle
- oracle 臨時表的使用Oracle
- ORACLE臨時表總結Oracle
- Oracle全域性臨時表Oracle
- oracle 臨時表空間Oracle
- oracle臨時表空間Oracle
- oracle臨時表的用法Oracle
- Oracle 臨時表空間概念Oracle
- oracle臨時表空間組Oracle
- Oracle臨時表GLOBAL TEMPORARY TABLEOracle
- oracle的臨時表空間Oracle
- Oracle Temp 臨時表空間Oracle
- oracle臨時表的用法(轉）Oracle
- Oracle 基礎 ----臨時表和物件表Oracle物件
- Oracle臨時表使用注意事項Oracle
- oracle臨時表空間相關Oracle
- oracle全域性臨時表的特性Oracle
- Oracle TEMP臨時表空間概念Oracle
- Oracle 臨時表空間的概念Oracle
- oracle臨時表實際應用Oracle
- ORACLE臨時表空間總結Oracle
- oracle 重建臨時表空間 tempfileOracle
- 刪掉Oracle臨時表空間Oracle
- oracle 臨時表間重建的方法Oracle
- oracle臨時表的用法總結Oracle
- 27、oracle的臨時表問題Oracle
- ORACLE臨時表空間的清理Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- Oracle臨時表的用法總結FLOracle
- Oracle資料庫開發——臨時表Oracle資料庫
- Oracle Temp臨時表空間處理Oracle
- ORACLE預設的臨時表空間Oracle
- oracle的臨時表空間temporary tablespaceOracle