Posted on 2006-03-20 23:37
ikingqu 閱讀(760)
評論(0) 編輯 收藏 所屬分類:
DataBase
Oracle基本數據類型存儲格式淺析（一）——字符類型
發表人:yangtingkun | 發表時間: 2004年十二月09日, 23:47
前一陣看完文檔，對oracle的基本數據類型的存儲格式有了一些了解，最近有做了一些測試進行了驗證。
打算整理總結一下，這一篇主要說明字符類型的存儲格式。主要包括char、varchar2和long等幾種類型。
?
SQL> create table test_char (char_col char(10), varchar_col varchar2(10), long_col long);
表已創建。
SQL> insert into test_char values ('abc', '123', ',fd');
已創建 1 行。
SQL> commit;
提交完成。
SQL> select rowid from test_char;
ROWID
------------------
AAAB3LAAFAAAAAgAAA
根據rowid的定義規則，第7～9位是表示的是數據文件，F表示5，而10～15位表示的是在這個數據文件中的第幾個BLOCK，g表示32。（rowid編碼相當于64進制。用A~Z a~z 0~9 + /共64個字符表示。A表示0，B表示1，……，a表示26，……，0表示52，……，+表示62，/表示63。）
我們根據計算的結果去dump這個block。
SQL> ALTER SYSTEM DUMP DATAFILE 5 BLOCK 32;
系統已更改。
打開產生的trace文件：
data_block_dump,data header at 0x3421064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x03421064
bdba: 0x01400020
???? 76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f82
avsp=0x1f6e
tosp=0x1f6e
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f82
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: --H-FL-- lb: 0x1? cc: 3
col? 0: [10]? 61 62 63 20 20 20 20 20 20 20
col? 1: [ 3]? 31 32 33
col? 2: [ 3]? 2c 66 64
end_of_block_dump
End dump data blocks tsn: 5 file#: 5 minblk 32 maxblk 32
觀察dump出來的結果，可以發現以下幾點：
1.對于每個字段，除了保存字段的值以外，還會保存當前字段中數據的長度。而且，oracle顯然沒有把字段的長度定義或類型定義保存在block中，這些信息保存在oracle的數據字典里面。
2. 根據dump的結果，可以清楚的看到，字符類型在數據庫中是以ascii格式存儲的。
SQL> select chr(to_number('61', 'xx')) from dual;
CH
--
a
3.char類型為定長格式，存儲的時候會在字符串后面填補空格，而varchar2和long類型都是變長的。
SQL> SELECT DUMP(CHAR_COL, 16) D_CHAR FROM TEST_CHAR;
D_CHAR
-------------------------------------------------------------
Typ=96 Len=10: 61,62,63,20,20,20,20,20,20,20
SQL> SELECT DUMP(VARCHAR_COL, 16) D_VARCHAR2 FROM TEST_CHAR;
D_VARCHAR2
-------------------------------------------------------------
Typ=1 Len=3: 31,32,33
SQL> SELECT DUMP(LONG_COL, 16) D_VARCHAR2 FROM TEST_CHAR;
SELECT DUMP(LONG_COL, 16) D_VARCHAR2 FROM TEST_CHAR
??????????? *
ERROR 位于第 1 行:
ORA-00997: 非法使用 LONG 數據類型
由于DUMP不支持LONG類型，因此我們使用了alter system dump block的方式，通過比較兩種方式得到的結果，發現DUMP()函數不但方便，結果清晰，而且指出了進行DUMP的數據類型，在以后的例子中，除非必要的情況，否則都會采用DUMP()函數的方式進行說明。
下面看一下插入中文的情況，首先看一下數據庫的字符集
SQL> select name, value$ from sys.props$ where name like '%CHARACTERSET%';
NAME?????????????????????????? VALUE$
------------------------------ ------------------------------
NLS_CHARACTERSET?????????????? ZHS16GBK
NLS_NCHAR_CHARACTERSET???????? AL16UTF16
SQL> insert into test_char values ('定長', '變長', null);
已創建 1 行。
SQL> SELECT DUMP(CHAR_COL, 16) D_CHAR FROM TEST_CHAR;
D_CHAR
----------------------------------------------------------------
Typ=96 Len=10: 61,62,63,20,20,20,20,20,20,20
Typ=96 Len=10: b6,a8,b3,a4,20,20,20,20,20,20
SQL> SELECT DUMP(VARCHAR_COL, 16) D_VARCHAR2 FROM TEST_CHAR;
D_VARCHAR2
----------------------------------------------------------------
Typ=1 Len=3: 31,32,33
Typ=1 Len=4: b1,e4,b3,a4
根據dump結果，可以清楚的看出，普通英文字符和標點用一個字節表示，而中文字符或中文標點需要兩個字節來表示。
下面，對比一下nchar和nvarchar2與char、varchar2類型有什么不同。
SQL> create table test_nchar (nchar_col nchar(10), nvarchar_col nvarchar2(10));
表已創建。
SQL> insert into test_nchar values ('nchar定長', 'nvarchar變長');
已創建 1 行。
從這里已經可以看出一些不同了，如果按照剛才中文的計算方法，'nvarchar變長'的長度是8+2*2=12已經超過了數據類型定義的大小，可是為什么插入成功了？
還是dump一下看看結果吧。
SQL> select dump(nchar_col, 16) from test_nchar;
DUMP(NCHAR_COL,16)
--------------------------------------------------------------
Typ=96 Len=20: 0,6e,0,63,0,68,0,61,0,72,5b,9a,95,7f,0,20,0,20,0,20
SQL> select dump(nvarchar_col, 16) from test_nchar;
DUMP(NVARCHAR_COL,16)
--------------------------------------------------------------
Typ=1 Len=20: 0,6e,0,76,0,61,0,72,0,63,0,68,0,61,0,72,53,d8,95,7f
這下就明白了，雖然仍然是采用ascii碼存儲，但是nchar使用的AL16UTF16字符集，編碼長度變為2個字節。這樣中文使用兩個字節，對于可以用一個字節就表示的英文字符，采用了高位補0的方式湊足2位，這樣，對于采用AL16UTF16字符集的nchar類型，無論中文還是英文都用2位字符表示。因此'nvarchar變長'的長度是10，并沒有超過數據類型的限制。
==============================================================
Oracle基本數據類型存儲格式淺析（二）——數字類型
發表人:yangtingkun | 發表時間: 2004年十二月14日, 22:57
這篇文章主要描述NUMBER類型的數據和如何在數據庫中存儲的。
Oracle的NUMBER類型最多由三個部分構成，這三個部分分別是最高位表示位、數據部分、符號位。其中負數包含符號位，正數不會包括符號位。另外，數值0比較特殊，它只包含一個數值最高位表示位80，沒有數據部分。
正數的最高位表示位大于80，負數的最高位表示位小于80。其中一個正數的最高位是個位的話，則最高位表示位為C1，百位、萬位依次為C2、C3，百分位、萬分為依次為C0、BF。一個負數的最高位為個位的話，最高位表示位為3E，百位、萬位依次為3D、3C，百分位、萬分位依次為3F、40。
數據部分每一位都表示2位數。這個兩位數可能是從0到99，如果是數據本身是正數，則分別用二進制的1到64表示，如果數據本身是負數，則使用二進制65到2表示。
符號位用66表示。
上面的這些是我通過DUMP結果總結出來的，對于上面提到的這些關系常數，Oracle之所以這樣選擇是有道理的，我們后面根據例子也可以推導出來，而且會進一步說明為什么會采用這種方式表示。這里列出的意思是使大家先對NUMBER類型數據有一個大概的了解。
下面我們通過一個例子詳細說明：
?
SQL> CREATE TABLE TEST_NUMBER (NUMBER_COL NUMBER);
表已創建。
SQL> INSERT INTO TEST_NUMBER VALUES (0);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (1);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (2);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (25);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (123);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (4100);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (132004078);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (2.01);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (0.3);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (0.00000125);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (115.200003);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (-1);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (-5);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (-20032);
已創建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (-234.432);
已創建 1 行。
SQL> COMMIT;
提交完成。
SQL> COL D_NUMBER FORMAT A50
SQL> SELECT NUMBER_COL, DUMP(NUMBER_COL, 16) D_NUMBER FROM TEST_NUMBER;
NUMBER_COL D_NUMBER
---------- --------------------------------------------------
???????? 0 Typ=2 Len=1: 80
???????? 1 Typ=2 Len=2: c1,2
???????? 2 Typ=2 Len=2: c1,3
??????? 25 Typ=2 Len=2: c1,1a
?????? 123 Typ=2 Len=3: c2,2,18
????? 4100 Typ=2 Len=2: c2,2a
?132004078 Typ=2 Len=6: c5,2,21,1,29,4f
????? 2.01 Typ=2 Len=3: c1,3,2
??????? .3 Typ=2 Len=2: c0,1f
?.00000125 Typ=2 Len=3: be,2,1a
115.200003 Typ=2 Len=6: c2,2,10,15,1,4
??????? -1 Typ=2 Len=3: 3e,64,66
??????? -5 Typ=2 Len=3: 3e,60,66
??? -20032 Typ=2 Len=5: 3c,63,65,45,66
? -234.432 Typ=2 Len=6: 3d,63,43,3a,51,66
已選擇15行。
?
??? 下面根據例子得到的結果，對每行進行說明。首先說明兩點基本的。DUMP函數返回的TYPE＝2表示DUMP的數據類型是NUMBER，LENGTH＝N表示數值在數據庫中存儲的長度是N。
1.DUMP(0)的結果是0x80，在前面已經提到，0只有高位表示位，沒有數據位。由于0的特殊，既不屬于正數，也不屬于負數，因此使用高位表示位用80表示就足夠了，不會和其它數據沖突，Oracle出于節省空間的考慮將后面數據部分省掉了。但是為什么Oracle選擇0x80表示0呢？我們知道正數和負數互為相反數，每個正數都有一個對應的負數。因此如果我們要使用編碼表示數值，則表示正數和負數的編碼應該各占一半，這樣才能保證使Oracle表示數據范圍是合理的。而0x80的二進制編碼是1000 0000，正好是一個字節編碼最大值的一半，因此，Oracle選擇0x80來表示0，是十分有道理的。
2.DUMP(1)的結果是0xc102，0xc1表示了最高位個位，0x2表示數值是1。首先，Oracle為什么用C1表示個位呢？其實，道理和剛才的差不多。采用科學計數法，任何一個實數S都可以描述為A.B×10n，A表示整數部分，B表示小數部分，而N表示10的指數部分。當S大于1時，N大于等于0，S小于1時，N小于0。也就是說，采用指數的方式表示，N大于0和N小于0的情況各占一半左右時，Oracle所表示的范圍最廣。因此，Oracle選擇了C1表示個位是最高位的情況。
SQL> SELECT TO_CHAR(ROUND(TO_NUMBER('81', 'XXX') + (TO_NUMBER('FF', 'XXX') - TO_NUMBER('81', 'XXX') + 1)/2), 'XX') FROM DUAL;
TO_
---
?C1
?
為什么ORACLE使用0x2表示1，而不直接使用0x1表示1呢？Oracle每個字節表示2位數，因此對于這個2位數，出現的可能是0～99共100種可能，問題出在0這里。Oracle底層是用C語言實現的，我們知道二進制0在C語言中用作字符串終結符，Oracle為了避免這個問題，因此使用了0x1表示0，并依次類推，使用0x64表示99。
3.DUMP(2)的結果是0xc103。
4.DUMP(25)的結果是0xc11a。前面提到，數據部分是以2位為最小單位保存的。因此對于25來說，最高位表示位仍然是個位，個位上的值是25，根據上面推出的規則，25在存儲為0xc11a。
SQL> SELECT TO_CHAR(25 + 1, 'xx') FROM DUAL;
TO_
---
?1a
?
5.DUMP(123)的結果是0xc20218。由于123最高為是百位，所以最高位表示位為0xc2，百位上是1，用0x02表示，個位上是23，用0x18表示。
6.DUMP(4100)的結果是0xc22a。
注意一點，如果數字最后數位上如果是0，Oracle出于節省空間的考慮不會存儲。比如：4100只保存百位上的41，12000000只保存百位位上的12，512000只保存萬位上的51和百位上的20。
7.DUMP(132004078)的結果是0xc5022101294f。最高位是億位，因此用0xC5表示，億位上是1用0x02表示，百位位上是32用0x21表示，萬位上是0用0x01表示，百位上是40用0x29表示，個位上78用0x4F表示。
注意：中間數位上的0不能省略。
8.DUMP(2.01)的結果是0xc10302。最高位是個位用0xC1表示，個位上是2用0x03表示，百分位上是1用0x02表示。
注意：個位下面一位是百分位不是十分位。
9.DUMP(0.3)的結果是0xc01f。最高位是百分位，使用0xC0表示，百分位上是30用0x1F表示。
10.DUMP(0.00000125)的結果是0xbe021a。最高位是百萬分位，用0xBE表示，最高位上的1用0x02表示，25用0x1a表示。
11.DUMP(115.200003)的結果是0xc20210150104。
12.DUMP(-1)的結果是0x3e6466。最高位個位，用0x3E表示，64表示個位上是1，66是符號位，表示這個數是負數。
負數和正數互為相反數，負數的最高位表示位和它對應的相反數的最高位相加的值是FF。1的最高位表示位是C1，-1的最高位表示位是3E。負數中1用64表示。負數中的數值和它相反數的數據相加是0x66，也就是符號位。正數1用0x02表示，負數1用0x64表示，二者相加是0x66。負數多個一個標識位，用0x66表示。由于正數的表示范圍是0x01到0x64，負數的表示范圍是0x65到0x02。因此，不會在表示數字時出現的0x66表示。
13.DUMP(-5)的結果是0x3e6066。0x3e表示最高位是個位，0x60表示個位上是5，0x66是符號標識位。0x3E加0xC1是0xFF。0x60加0x06的結果是0x66。
14.DUMP(-20032)的結果是0x3c63654566。最高位是萬位，正數的萬位是0xC3，因此負數的萬位是0x3C。萬位上是2，正數用0x03表示，負數為0x63，百位上是0，正數用0x01表示，負數使用0x65表示，個位上是32，正數用0x21表示，負數使用0x45表示。0x66是負數表示位。
15.DUMP(-234.432)的結果是0x3d63433a5166。
?
根據Oracle的存儲特性，還可以推出Oracle的number類型的取值范圍。
Oracle的concept上是這樣描述的：
The following numbers can be stored in a NUMBER column:
Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits.
Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits.
Zero.
下面來推導出取值范圍。
來看符號位，0xC1表示個位。
SQL> select to_number('ff', 'xxx') - to_number('c1', 'xxx') from dual;
TO_NUMBER('FF','XXX')-TO_NUMBER('C1','XXX')
-------------------------------------------
???????????????????????????????????????? 62
由于Oracle是兩位、兩位存儲的，因此最高位相當于62×2=124，而且最高位上最大值是99，因此正數的最大值為9.999……×10125。
SQL> select to_number('c1', 'xxx') - to_number('80', 'xxx') from dual;
TO_NUMBER('C1','XXX')-TO_NUMBER('80','XXX')
-------------------------------------------
???????????????????????????????????????? 65
最高位相當于65×2=130，因此正數的最小值為1×10-130。
負數和正數在各使用了一半的編碼，因此具有相同的極值范圍。
==============================================================
Oracle基本數據類型存儲格式淺析（三）——日期類型（一）
發表人:yangtingkun | 發表時間: 2004年十二月15日, 14:00
這篇文章描述DATE類型的數據在Oracle中是以何種格式存放的。
下面通過一個例子進行說明。
?
SQL> create table test_date (date_col date);
表已創建。
SQL> insert into test_date values (to_date('2000-1-1 0:0:0', 'yyyy-mm-dd hh24:mi:ss'));
已創建 1 行。
SQL> insert into test_date values (to_date('1-1-1 0:0:0', 'yyyy-mm-dd hh24:mi:ss'));
已創建 1 行。
SQL> insert into test_date values (to_date('-1-1-1 0:0:0', 'syyyy-mm-dd hh24:mi:ss'));
已創建 1 行。
SQL> insert into test_date values (to_date('-101-1-1 0:0:0', 'syyyy-mm-dd hh24:mi:ss'));
已創建 1 行。
SQL> insert into test_date values (to_date('-4712-1-1 0:0:0', 'syyyy-mm-dd hh24:mi:ss'));
已創建 1 行。
SQL> insert into test_date values (to_date('9999-12-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'));
已創建 1 行。
SQL> insert into test_date values (sysdate);
已創建 1 行。
SQL> insert into test_date values (to_date('-4713-1-1 0:0:0', 'syyyy-mm-dd hh24:mi:ss'));
insert into test_date values (to_date('-4713-1-1 0:0:0', 'syyyy-mm-dd hh24:mi:ss'))
????????????????????????????????????? *
ERROR 位于第 1 行:
ORA-01841: （全）年度值必須介于 -4713 和 +9999 之間，且不為 0
SQL> insert into test_date values (to_date('0000-1-1 0:0:0', 'yyyy-mm-dd hh24:mi:ss'));
insert into test_date values (to_date('0000-1-1 0:0:0', 'yyyy-mm-dd hh24:mi:ss'))
????????????????????????????????????? *
ERROR 位于第 1 行:
ORA-01841: （全）年度值必須介于 -4713 和 +9999 之間，且不為 0
SQL> col dump_date format a80
SQL> select to_char(date_col, 'syyyy-mm-dd hh24:mi:ss'), dump(date_col) dump_date from test_date;
TO_CHAR(DATE_COL,'SY DUMP_DATE
-------------------- ---------------------------------------
?2000-01-01 00:00:00 Typ=12 Len=7: 120,100,1,1,1,1,1
?0001-01-01 00:00:00 Typ=12 Len=7: 100,101,1,1,1,1,1
-0001-01-01 00:00:00 Typ=12 Len=7: 100,99,1,1,1,1,1
-0101-01-01 00:00:00 Typ=12 Len=7: 99,99,1,1,1,1,1
-4712-01-01 00:00:00 Typ=12 Len=7: 53,88,1,1,1,1,1
?9999-12-31 23:59:59 Typ=12 Len=7: 199,199,12,31,24,60,60
?2004-12-15 13:56:19 Typ=12 Len=7: 120,104,12,15,14,57,20
已選擇7行。
?
通過最后兩條語句已經可以看出Oracle的DATE類型的取值范圍是公元前4712年1月1日至公元9999年12月31日。而且根據日期的特定，要不然是公元1年，要不然是公元前1年，不會出現0年的情況。
日期類型長度是7，7個字節分別表示世紀、年、月、日、時、分和秒。
由于不會出現0的情況，月和日都是按照原值存儲的，月的范圍是1～12，日的范圍是1～31。
由于時、分、秒都會出現0的情況，因此存儲時采用原值加1的方式。0時保存為1，13時保存為14，23時保存為24。分和秒的情況與小時類似。小時的范圍是0～23，在數據庫中以1～24保存。分和秒的范圍都是0～59，在數據庫中以1～60保存。
年和世紀的情況相對比較復雜，可分為公元前和公元后兩種情況。由于最小的世紀的值是-47（公元前4712年），最大值是99（公元9999年）。為了避免負數的產生，oracle把世紀加100保存在數據庫中。公元2000年，世紀保存為120，公元9999年，世紀保存為199，公元前101年，世紀保存為99（100+(-1)），公元前4712年，世紀保存為53（100+(-47)）。
注意，對于公元前1年，雖然已經是公元前了，但是表示世紀的前兩位的值仍然是0，因此，這時的保存的世紀的值仍然是100。世紀的范圍是-47～99，保存的值是53～199。
年的保存與世紀的保存方式類似，也把年的值加上100進行保存。對于公元2000年，年保持為100，公元1年保存為101，公元2004年保存為104，公元9999年保存為199，公元前1年，保存為99（100+(-1)），公元前101年，保存為99（100+(-1)），公元前4712年保存為88（100+(-12)）。對于公元前的年，保存的值總是小于等于100，對于公元后的年，保存的值總是大于等于100。年的范圍是0～99，保存的值是1～199。
注意：一般的世紀，都包含了100年，而對于0世紀，由于包含公元前和公元后兩部分且不包含0年，因此包含了198年。
==============================================================
Oracle基本數據類型存儲格式淺析（三）——日期類型（二）
發表人:yangtingkun | 發表時間: 2004年十二月16日, 18:03
這篇文章描述TIMESTAMP類型的數據在Oracle中是以何種格式存放的。
下面通過一個例子進行說明。
?
SQL> create table test_time (col_time timestamp);
表已創建。
SQL> insert into test_time values (to_timestamp('0001-1-1 0:0:0.0', 'syyyy-mm-dd hh24:mi:ss.ff'));
已創建 1 行。
SQL> insert into test_time values (to_timestamp('2000-1-1 0:0:0.0', 'syyyy-mm-dd hh24:mi:ss.ff'));
已創建 1 行。
SQL> insert into test_time values (to_timestamp('9999-12-31 23:59:59.999999', 'syyyy-mm-dd hh24:mi:ss.ff'));
已創建 1 行。
SQL> insert into test_time values (to_timestamp('-0001-1-1 0:0:0.0', 'syyyy-mm-dd hh24:mi:ss.ff'));
已創建 1 行。
SQL> insert into test_time values (to_timestamp('-0100-3-4 13:2:3.234015', 'syyyy-mm-dd hh24:mi:ss.ff'));
已創建 1 行。
SQL> insert into test_time values (systimestamp);
已創建 1 行。
SQL> insert into test_time values (to_timestamp('2000-1-1 0:0:0.123456789', 'syyyy-mm-dd hh24:mi:ss.ff9'));
已創建 1 行。
SQL> commit;
提交完成。
SQL> select to_char(col_time, 'syyyy-mm-dd hh24:mi:ss.ff9') time, dump(col_time) dump_time
? 2? from test_time;
TIME?????????????????????????? DUMP_TIME
------------------------------ ----------------------------------------------------
?0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,101,1,1,1,1,1
?2000-01-01 00:00:00.000000000 Typ=180 Len=7: 120,100,1,1,1,1,1
?9999-12-31 23:59:59.999999000 Typ=180 Len=11: 199,199,12,31,24,60,60,59,154,198,24
-0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,99,1,1,1,1,1
-0100-03-04 13:02:03.234015000 Typ=180 Len=11: 99,100,3,4,14,3,4,13,242,201,24
?2004-12-15 16:14:52.738000000 Typ=180 Len=11: 120,104,12,15,17,15,53,43,252,252,128
?2000-01-01 00:00:00.123457000 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,232
已選擇7行。
與DATE類型對比可以發現，對于TIMESTAMP類型，如果不包含微秒信息或者微秒值為0，那么存儲結果和DATE完全相同。當微秒值為0時，Oracle為了節省空間，不會保存微秒信息。
如果毫秒值不為0，Oracle把微秒值當作一個9位數的數字來保存。
比如999999000，保存為59,154,198,24。234015000保存為13,242,201,24。
SQL> select to_char(999999000, 'xxxxxxxxxx') from dual;
TO_CHAR(999
-----------
?? 3b9ac618
SQL> select to_number('3b', 'xxx') one, to_number('9a', 'xxx') two,
? 2? to_number('c6', 'xxx') three, to_number('18', 'xxx') four from dual;
?????? ONE??????? TWO????? THREE?????? FOUR
---------- ---------- ---------- ----------
??????? 59??????? 154??????? 198???????? 24
SQL> select to_char(234015000, 'xxxxxxxx') from dual;
TO_CHAR(2
---------
? df2c918
SQL> select to_number('d', 'xxx') one, to_number('f2', 'xxx') two,
? 2? to_number('c9', 'xxx') three, to_number('18', 'xxx') four from dual;
?????? ONE??????? TWO????? THREE?????? FOUR
---------- ---------- ---------- ----------
??????? 13??????? 242??????? 201???????? 24
?
另外，注意一點，不指定精度的情況下，TIMESTAMP默認取6位。長度超過6位，會四舍五入到6位。如果希望保存9位的TIMESTAMP，必須明確指定精度。
SQL> alter table test_time modify (col_time timestamp(9));
表已更改。
SQL> insert into test_time values (to_timestamp('2000-1-1 0:0:0.123456789', 'syyyy-mm-dd hh24:mi:ss.ff9'));
已創建 1 行。
SQL> select to_char(col_time, 'syyyy-mm-dd hh24:mi:ss.ff9') time, dump(col_time) dump_time
? 2? from test_time;
TIME?????????????????????????? DUMP_TIME
------------------------------ ---------------------------------------------------
?0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,101,1,1,1,1,1
?2000-01-01 00:00:00.000000000 Typ=180 Len=7: 120,100,1,1,1,1,1
?9999-12-31 23:59:59.999999000 Typ=180 Len=11: 199,199,12,31,24,60,60,59,154,198,24
-0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,99,1,1,1,1,1
-0100-03-04 13:02:03.234015000 Typ=180 Len=11: 99,100,3,4,14,3,4,13,242,201,24
?2004-12-15 16:14:52.738000000 Typ=180 Len=11: 120,104,12,15,17,15,53,43,252,252,128
?2000-01-01 00:00:00.123457000 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,232
?2000-01-01 00:00:00.123456789 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,21
已選擇8行。
==============================================================
Oracle基本數據類型存儲格式淺析（三）——日期類型（三）
發表人:yangtingkun | 發表時間: 2004年十二月17日, 17:00
如果直接在SQL語句中對SYSDATE或由TO_DATE函數生成日期進行DUMP操作，會發現得到的結果與DUMP數據庫中保存的日期的結果不一樣。
?
SQL> truncate table test_date;
表已截掉。
SQL> insert into test_date values (to_date('2004-12-17 16:42:42', 'syyyy-mm-dd hh24:mi:ss'));
已創建 1 行。
SQL> col dump_date format a65
SQL> select to_char(date_col, 'syyyy-mm-dd hh24:mi:ss') dat, dump(date_col) dump_date from test_date;
DAT????????????????? DUMP_DATE
-------------------- ---------------------------------------------------------
?2004-12-17 16:42:42 Typ=12 Len=7: 120,104,12,17,17,43,43
SQL> select to_char(to_date('2004-12-17 16:42:42', 'syyyy-mm-dd hh24:mi:ss'), 'syyyy-mm-dd hh24:mi:ss') dat,
? 2? dump(to_date('2004-12-17 16:42:42', 'syyyy-mm-dd hh24:mi:ss')) dump_date from dual;
DAT????????????????? DUMP_DATE
-------------------- ---------------------------------------------------------
?2004-12-17 16:42:42 Typ=13 Len=8: 212,7,12,17,16,42,42,0
存儲在數據庫中的DATE類型是12，而直接在SQL中使用的DATE類型是13。而且二者的長度以及表示方式都不相同。這兩種類型的不同指出主要體現在兩點：一：時、分、秒的表示不同；二、世紀和年的表示不同。
SQL中使用DATE的時分秒沒有采用加1存儲方式，而且原值存儲。
SQL中使用DATE沒有采用世紀、年的方式保持，而是采用了按數值保存的方式。第一位表示低位，第二位表示高位。低位表示最大的值是255。如上面的例子中，212+7×256=2004。
SQL> select to_char(to_date('-2004-12-17 16:42:42', 'syyyy-mm-dd hh24:mi:ss'), 'syyyy-mm-dd hh24:mi:ss') dat,
? 2? dump(to_date('-2004-12-17 16:42:42', 'syyyy-mm-dd hh24:mi:ss')) dump_date from dual;
DAT????????????????? DUMP_DATE
-------------------- ---------------------------------------------------
-2004-12-17 16:42:42 Typ=13 Len=8: 44,248,12,17,16,42,42,0
SQL> select dump(to_date('-1-1-1', 'syyyy-mm-dd')) from dual;
DUMP(TO_DATE('-1-1-1','SYYYY-MM-D
---------------------------------
Typ=13 Len=8: 255,255,1,1,0,0,0,0
?
對于公元前的日期，Oracle從255，255開始保存。公元前的年的保存的值和對應的公元后的年的值相加的和是256，255。如上例中的公元2004年和公元前2004年的值相加：212+44=256，7+248=255。
SQL中DATE類型最后還包括一個0，似乎目前沒有使用。
==============================================================
Oracle基本數據類型存儲格式淺析（三）——日期類型（四）
發表人:yangtingkun | 發表時間: 2005年一月12日, 02:26
本文對TIMESTAMP WITH LOCAL TIME ZONE和TIMESTAMP WITH TIME ZONE類型的存儲格式進行簡單的說明。
?
SQL> CREATE TABLE TEST_TIMESTAMP(TIME1 TIMESTAMP(9), TIME2 TIMESTAMP(6) WITH LOCAL TIME ZONE,
? 2? TIME3 TIMESTAMP(4) WITH TIME ZONE);
表已創建。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已創建 1 行。
SQL> SELECT * FROM TEST_TIMESTAMP;
TIME1
----------------------------------------------------
TIME2
----------------------------------------------------
TIME3
----------------------------------------------------
11-1月 -05 11.08.15.027000000 下午
11-1月 -05 11.08.15.027000 下午
11-1月 -05 11.08.15.0270 下午 +08:00
SQL> SELECT DUMP(TIME1, 16), DUMP(TIME2, 16), DUMP(TIME3, 16) FROM TEST_TIMESTAMP;
DUMP(TIME1,16)
-------------------------------------------------------------
DUMP(TIME2,16)
-------------------------------------------------------------
DUMP(TIME3,16)
-------------------------------------------------------------
Typ=180 Len=11: 78,69,1,b,18,9,10,1,9b,fc,c0
Typ=231 Len=11: 78,69,1,b,18,9,10,1,9b,fc,c0
Typ=181 Len=13: 78,69,1,b,10,9,10,1,9b,fc,c0,1c,3c
可以發現，如果客戶端和數據庫中的時區是一致的，那么TIMESTAMP和TIMESTAMP WITH LOCAL TIME ZONE存儲的數據是完全一樣的。
TIMESTAMP WITH TIME ZONE則略有不同，它保存的是0時區的時間，和所處的時區信息。
修改客戶端主機的時區，由東8區（+8區）改為0時區。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已創建 1 行。
修改客戶端主機的時區，改為西5區（-5時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已創建 1 行。
修改客戶端主機的時區，改為西12區（-12時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已創建 1 行。
修改客戶端主機的時區，改為東13區（+13時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已創建 1 行。
修改客戶端主機的時區，改為西3.5區（-3.5時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已創建 1 行。
修改客戶端主機的時區，改為東9.5區（+9.5時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已創建 1 行。
SQL> COMMIT;
提交完成。
修改客戶端主機的時區，改回東8區（+8時區）。
SQL> SELECT * FROM TEST_TIMESTAMP;
TIME1
-----------------------------------------------
TIME2
-----------------------------------------------
TIME3
-----------------------------------------------
11-1月 -05 11.08.15.027000000 下午
11-1月 -05 11.08.15.027000 下午
11-1月 -05 11.08.15.0270 下午 +08:00
11-1月 -05 03.11.43.746000000 下午
11-1月 -05 11.11.43.746000 下午
11-1月 -05 03.11.43.7460 下午 +00:00
11-1月 -05 10.14.08.987000000 上午
11-1月 -05 11.14.08.987000 下午
11-1月 -05 10.14.08.9870 上午 -05:00
11-1月 -05 03.15.01.732000000 上午
11-1月 -05 11.15.01.732000 下午
11-1月 -05 03.15.01.7320 上午 -12:00
12-1月 -05 04.20.21.522000000 上午
11-1月 -05 11.20.21.522000 下午
12-1月 -05 04.20.21.5220 上午 +13:00
11-1月 -05 02.15.16.567000000 下午
12-1月 -05 01.45.16.567000 上午
11-1月 -05 02.15.16.5670 下午 -03:30
12-1月 -05 03.16.54.992000000 上午
12-1月 -05 01.46.54.992000 上午
12-1月 -05 03.16.54.9920 上午 +09:30
已選擇7行。
SQL> SELECT DUMP(TIME1, 16), DUMP(TIME2, 16), DUMP(TIME3, 16) FROM TEST_TIMESTAMP;
DUMP(TIME1,16)
-------------------------------------------------------------
DUMP(TIME2,16)
-------------------------------------------------------------
DUMP(TIME3,16)
-------------------------------------------------------------
Typ=180 Len=11: 78,69,1,b,18,9,10,1,9b,fc,c0
Typ=231 Len=11: 78,69,1,b,18,9,10,1,9b,fc,c0
Typ=181 Len=13: 78,69,1,b,10,9,10,1,9b,fc,c0,1c,3c
Typ=180 Len=11: 78,69,1,b,10,c,2c,2c,77,e,80
Typ=231 Len=11: 78,69,1,b,18,c,2c,2c,77,e,80
Typ=181 Len=13: 78,69,1,b,10,c,2c,2c,77,e,80,14,3c
Typ=180 Len=11: 78,69,1,b,b,f,9,3a,d4,6c,c0
Typ=231 Len=11: 78,69,1,b,18,f,9,3a,d4,6c,c0
Typ=181 Len=13: 78,69,1,b,10,f,9,3a,d4,6c,c0,f,3c
Typ=180 Len=11: 78,69,1,b,4,10,2,2b,a1,6f,0
Typ=231 Len=11: 78,69,1,b,18,10,2,2b,a1,6f,0
Typ=181 Len=13: 78,69,1,b,10,10,2,2b,a1,6f,0,8,3c
Typ=180 Len=11: 78,69,1,c,5,15,16,1f,1d,16,80
Typ=231 Len=11: 78,69,1,b,18,15,16,1f,1d,16,80
Typ=181 Len=13: 78,69,1,b,10,15,16,1f,1d,16,80,21,3c
Typ=180 Len=11: 78,69,1,b,f,10,11,21,cb,bb,c0
Typ=231 Len=11: 78,69,1,c,2,2e,11,21,cb,bb,c0
Typ=181 Len=13: 78,69,1,b,12,2e,11,21,cb,bb,c0,11,1e
Typ=180 Len=11: 78,69,1,c,4,11,37,3b,20,b8,0
Typ=231 Len=11: 78,69,1,c,2,2f,37,3b,20,b8,0
Typ=181 Len=13: 78,69,1,b,12,2f,37,3b,20,b8,0,1d,5a
?
SQL> SELECT TO_NUMBER('1C', 'XXX'), TO_NUMBER('3C', 'XXX') FROM DUAL;
TO_NUMBER('1C','XXX') TO_NUMBER('3C','XXX')
--------------------- ---------------------
?????????????????? 28??????????????????? 60
SQL> SELECT TO_NUMBER('14', 'XXX'), TO_NUMBER('3C', 'XXX'), TO_NUMBER('143C', 'XXXXXXX') FROM DUAL;
TO_NUMBER('14','XXX') TO_NUMBER('3C','XXX')
--------------------- ---------------------
?????????????????? 20??????????????????? 60
SQL> SELECT TO_NUMBER('3C', 'XXX') , TO_NUMBER('1E', 'XXX'), TO_NUMBER('5A', 'XXX') FROM DUAL;
TO_NUMBER('3C','XXX') TO_NUMBER('1E','XXX') TO_NUMBER('5A','XXX')
--------------------- --------------------- -------------------
?????????????????? 60??????????????????? 30????????????????? 90
可以看出，修改時區會導致系統TIMESTAMP時間發生變化，但是對于TIMESTAMP WITH LOCAL TIME ZONE類型，總是將系統的時間轉化到數據庫服務器上時區的時間進行存儲。
TIMESTAMP WITH TIME ZONE保存的是當前時間轉化到0時區的對應的時間，并通過最后兩位來保存時區信息。
第一位表示時區的小時部分。0時區用0x14表示。東n區在這個基礎上加n，西n區在這個基礎上減n。我們所處的東8區表示為0x1C。西5區表示為0xF。
第二位表示時區的分鐘部分。標準是0x3C，即60分鐘。對于東時區的半區，在這個基礎上加上30分鐘，如果是西時區，則減去30分鐘。
==============================================================
Oracle基本數據類型存儲格式淺析（四）——ROWID類型（一）
發表人:yangtingkun | 發表時間: 2004年十二月21日, 19:14
Oracle的ROWID用來唯一標識表中的一條記錄，是這條數據在數據庫中存放的物理地址。
Oracle的ROWID分為兩種：物理ROWID和邏輯ROWID。索引組織表使用邏輯ROWID，其他類型的表使用物理ROWID。其中物理ROWID在Oracle的8版本中進行了擴展，Oracle7及以下版本使用約束ROWID，Oracle8及以上版本使用擴展ROWID。本文描述物理擴展ROWID，由于約束ROWID僅僅是為了兼容早期版本，因此不做討論。
SQL> create table test_rowid (id number, row_id rowid);
表已創建。
SQL> insert into test_rowid values (1, null);
已創建 1 行。
SQL> update test_rowid set row_id = rowid where id = 1;
已更新 1 行。
SQL> commit;
提交完成。
SQL> select rowid, row_id from test_rowid;
ROWID????????????? ROW_ID
------------------ ------------------
AAABnRAAGAAAACWAAA AAABnRAAGAAAACWAAA
Oracle的物理擴展ROWID有18位，每位采用64位編碼，分別用A~Z、a~z、0~9、+、/共64個字符表示。A表示0，B表示1，……Z表示25，a表示26，……z表示51，0表示52，……，9表示61，+表示62，/表示63。
ROWID具體劃分可以分為4部分。
1.OOOOOO：前6位表示DATA OBJECT NUMBER，將起轉化位數字后匹配DBA_OBJECTS中的DATA_OBJECT_ID，可以確定表信息。
如上面例子中的DATA OBJECT NUMBER是AAABnR，轉化位數字是1×64×64 ＋39×64 ＋ 17。
SQL> select owner, object_name from dba_objects
? 2? where data_object_id = 1*64*64 + 39*64 + 17;
OWNER????????????????????????? OBJECT_NAME
------------------------------ -----------------------------
YANGTK???????????????????????? TEST_ROWID
2.FFF：第7到9位表示相對表空間的數據文件號。
上面的例子中是AAG，表示數據文件6。
SQL> select file_name, tablespace_name from dba_data_files where relative_fno = 6;
FILE_NAME???????????????????????????????????? TABLESPACE_NAME
--------------------------------------------- ---------------
E:ORACLEORADATATESTYANGTK01.DBF?????????? YANGTK
3.BBBBBB：第10到15位表示這條記錄在數據文件中的第幾個BLOCK中。
上面的例子是AAAACW，轉化位數字是2×64＋22，表示這條記錄在數據文件中的第150個BLOCK。
4.RRR：最后3位表示這條記錄是BLOCK中的第幾條記錄。
上面的例子是AAA，表示第0條記錄（總是從0開始計數）。
SQL> alter system dump datafile 6 block 150;
系統已更改。
SQL> select row_id, dump(row_id, 16) dump_rowid from test_rowid;
ROW_ID???????????? DUMP_ROWID
------------------ -------------------------------------------------
AAABnRAAGAAAACWAAA Typ=69 Len=10: 0,0,19,d1,1,80,0,96,0,0
?
找到對應的dump文件，可以發現類型的信息
*** 2004-12-21 17:58:26.000
*** SESSION ID:(13.91) 2004-12-21 17:58:26.000
Start dump data blocks tsn: 6 file#: 6 minblk 150 maxblk 150
buffer tsn: 6 rdba: 0x01800096 (6/150)
scn: 0x0000.2e389c16 seq: 0x01 flg: 0x06 tail: 0x9c160601
frmt: 0x02 chkval: 0xc97d type: 0x06=trans data
Block header dump:? 0x01800096
?Object id on Block? Y
?seg/obj: 0x19d1? csc: 0x00.2e389c0f? itc: 2? flg: O? typ: 1 - DATA
???? fsl: 0? fnx: 0x0 ver: 0x01
?
?Itl?????????? Xid????????????????? Uba???????? Flag? Lck??????? Scn/Fsc
0x01?? 0x0003.009.00000057? 0x0080004b.0042.56? --U-??? 1? fsc 0x0000.2e389c16
0x02?? 0x0000.000.00000000? 0x00000000.0000.00? ----??? 0? fsc 0x0000.00000000
?
data_block_dump,data header at 0x651105c
===============
tsiz: 0x3fa0
hsiz: 0x14
pbl: 0x0651105c
bdba: 0x01800096
???? 76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x3f89
avsp=0x3f7b
tosp=0x3f7b
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x3f89
block_row_dump:
tab 0, row 0, @0x3f89
tl: 17 fb: --H-FL-- lb: 0x1? cc: 2
col? 0: [ 2]? c1 02
col? 1: [10]? 00 00 19 d1 01 80 00 96 00 00
end_of_block_dump
End dump data blocks tsn: 6 file#: 6 minblk 150 maxblk 150
?
有時需要查看表的DUMP信息，但是很難準確定位表中數據開始于哪個BLOCK，根據ROWID中包含的信息就可以方便的找到起始BLOCK。
下面簡單描述一下ROWID類型是如何存儲的。
SQL> select row_id, dump(row_id, 16) dump_rowid from test_rowid;
ROW_ID???????????? DUMP_ROWID
------------------ -------------------------------------------------
AAABnRAAGAAAACWAAA Typ=69 Len=10: 0,0,19,d1,1,80,0,96,0,0
前4位表示ROWID的前6位，也就是DATA_OBJECT_ID信息。數據以數值的格式保存。
SQL> select to_number('19d1', 'xxxxxx') from dual;
TO_NUMBER('19D1','XXXXXX')
--------------------------
????????????????????? 6609
SQL> select 1*64*64 + 39*64 + 17 from dual;
1*64*64+39*64+17
----------------
??????????? 6609
這里存在一個問題，根據ROWID的取值范圍，OBJECT_DATA_ID最大的值是64的6次方，而根據DUMP，oracle只用了4位保存，因此取值范圍是256的4次方。
SQL> set numwid 12
SQL> select power(64, 6), power(256, 4), power(64, 6)/power(256, 4) from dual;
?POWER(64,6) POWER(256,4) POWER(64,6)/POWER(256,4)
------------ ------------ ------------------------
?68719476736?? 4294967296?????????????????????? 16
可見，OBJECT_DATA_ID的最大值是4294967296，當超過這個值時會出現重復的情況。（當然，現實中不大可能）。
后面4位比較特殊，是數據文件號和BLOCK數的“和”值構成。
數據文件的數值乘64后保存在5、6位上。
SQL> select to_number('0180', 'xxxx') from dual;
TO_NUMBER('0180','XXXX')
------------------------
???????????????????? 384
SQL> select 6*64 from dual;
??????? 6*64
------------
???????? 384
同時，6位BLOCK的值，也保存在這4位上，并與數據文件轉存結果相加。仍然是以數字格式存放。
SQL> select to_number('96', 'xxx') from dual;
TO_NUMBER('96','XXX')
---------------------
????????????????? 150
SQL> select 2*64 + 22 from dual;
?? 2*64+22
----------
?????? 150
由于采用兩位保存數據文件的值，且最小單位是64，因此，ROWID中可以保存的數據文件數是1024，超過1024會造成ROWID的重復。
SQL> select 256*256/64 from dual;
256*256/64
----------
????? 1024
由于BLOCK的值和數據文件共用這4位，因此BLOCK的第3位最大值應小于64，這樣才能保證ROWID的不重復。因此BLOCK值的最大值應該是4194304。
SQL> select 64*256*256 from dual;
64*256*256
----------
?? 4194304
最后兩位保存BLOCK中記錄的值。這個值的最大值是65536。
SQL> select 256*256 from dual;
?? 256*256
----------
???? 65536
?
下面看一個例子，Oracle是如何將相對文件號和BLOCK號“共享”第5、6字節的。
SQL> select blocks from user_segments where segment_name = 'TEST1';
??? BLOCKS
----------
???? 86016
SQL> select max(rowid), dump(max(rowid)) dump_rowid from test1;
MAX(ROWID)???????? DUMP_ROWID
------------------ -------------------------------------------
AAABy+AAJAAAU5EAAM Typ=69 Len=10: 0,0,28,190,2,65,78,68,0,12
SQL> select dbms_rowid.rowid_relative_fno('AAABy+AAJAAAU5EAAM') fno,
? 2? dbms_rowid.rowid_block_number('AAABy+AAJAAAU5EAAM') block_num from dual;
?????? FNO? BLOCK_NUM
---------- ----------
???????? 9????? 85572
SQL> select 9*64, 2*256+65 from dual;
????? 9*64?? 2*256+65
---------- ----------
?????? 576??????? 577
SQL> select 1*256*256 + 78*256 + 68 from dual;
1*256*256+78*256+68
-------------------
????????????? 85572
可以看到，5、6為的值除以64得到的商是相對文件號，余數是BLOCK號的高位，乘以65536后加上低兩位才是BLOCK號。
==============================================================
Oracle基本數據類型存儲格式淺析（四）——ROWID類型（二）
發表人:yangtingkun | 發表時間: 2004年十二月22日, 23:52
Oracle的文檔上沒有介紹邏輯ROWID的編碼規則，而且通過DUMP的結果也很難反推出編碼規則。因此，本文只簡單討論一下邏輯ROWID的存儲。
下面來看例子。
SQL> create table test_index (id number primary key, name varchar2(20)) organization index;
表已創建。
SQL> insert into test_index values (1, 'a');
已創建 1 行。
SQL> commit;
提交完成。
SQL> col dump_rowid format a60
SQL> select rowid, dump(rowid) dump_rowid from test_index;
ROWID?????????????????????? DUMP_ROWID
--------------------------- ----------------------------------------
*BAFAB4wCwQL+?????????????? Typ=208 Len=10: 2,4,1,64,7,140,2,193,2,254
邏輯ROWID的DUMP結果前兩位都是2和4，最后一位都是254，（我還沒有發現其他的情況），由于邏輯ROWID和主鍵的值有關，所以長度是不定的，因此應該是用來表示開始和結束的。
第3、4位和物理ROWID一樣，表示的是相對表空間的數據文件號乘以64的值。
第5、6位表示這條記錄在數據文件的第幾個BLOCK中。
從第7位開始到DUMP結果的倒數第二位，表示主鍵的值。首先是主鍵中第一個字段的長度，這里是2，然后是主鍵的值，由于是NUMBER類型，因此193，2表示數值1。如果是多個字段組成的主鍵，第一個字段之后是第二個字段的長度，然后是第二個字段的值……。
SQL> select (1*256 + 64)/64 from dual;
(1*256+64)/64
-------------
??????????? 5
SQL> select 7*256 + 140 from dual;
?7*256+140
----------
????? 1932
SQL> alter system dump datafile 5 block 1932;
系統已更改。
找到相應的dump文件，可以發現剛才插入的記錄。
Dump file f:oracleadmintest4udumptest4_ora_3828.trc
Thu Dec 23 00:17:53 2004
ORACLE V9.2.0.4.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.1 Service Pack 1, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Windows 2000 Version 5.1 Service Pack 1, CPU type 586
Instance name: test4
Redo thread mounted by this instance: 1
Oracle process number: 9
Windows thread id: 3828, image: ORACLE.EXE
*** 2004-12-23 00:17:53.361
*** SESSION ID:(8.82) 2004-12-23 00:17:53.301
Start dump data blocks tsn: 5 file#: 5 minblk 1932 maxblk 1932
buffer tsn: 5 rdba: 0x0140078c (5/1932)
scn: 0x0000.00e9f122 seq: 0x01 flg: 0x02 tail: 0xf1220601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:? 0x0140078c
?Object id on Block? Y
?seg/obj: 0x1e48? csc: 0x00.e9f113? itc: 2? flg: E? typ: 2 - INDEX
???? brn: 0? bdba: 0x1400789 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?? 0x0005.008.000000e7? 0x00800226.005c.24? --U-??? 1? fsc 0x0000.00e9f122
?
Leaf block dump
===============
header address 71963236=0x44a1264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8026=0x1f5a
kdxcoavs 7988
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8026] flag: K----, lock: 2
col 0; len 2; (2):? c1 02
tl: 5 fb: --H-FL-- lb: 0x0? cc: 1
col? 0: [ 1]
Dump of memory from 0x044A31C7 to 0x044A31C8
44A31C0????????? 61010100??????????????????????? [...a]???????
----- end of leaf block dump -----
End dump data blocks tsn: 5 file#: 5 minblk 1932 maxblk 1932
可以看到，根據DUMP結果的3、4、5、6位可以定位記錄的物理位置。
需要注意的是，索引組織表以主鍵的順序存儲數據，因此插入、更新和刪除數據都可能造成一條記錄的物理位置發生變化，這時通過ROWID中的DATAFILE和BLOCK的信息可能就無法正確定位到記錄的物理位置。當根據邏輯ROWID訪問索引組織表時，首先會根據DATAFILE和BLOCK信息去找到相應的BLOCK，檢查數據是否在這個BLOCK中，如果不在，就通過邏輯ROWID中的主鍵信息去通過索引掃描，找到這條記錄。這就是Oracle文檔在提到的physical guess。
下面看一個由字符串和日期組成聯合主鍵的例子。
SQL> create table test_index2 (id char(4), time date,
? 2? constraint pk_test_index2 primary key (id, time)) organization index;
表已創建。
SQL> insert into test_index2 values ('1', sysdate);
已創建 1 行。
SQL> col dump_rowid format a75
SQL> select rowid, dump(rowid) dump_rowid from test_index2;
ROWID??????????????????????? DUMP_ROWID
---------------------------- ------------------------------------------------------------------
*BAFAB5QEMSAgIAd4aAwXASMT/g? Typ=208 Len=20: 2,4,1,64,7,148,4,49,32,32,32,7,120,104,12,23,1,35,19,254
可以看出，第7位是字段id的長度4，然后是字符串1和三個空格的ASCII碼，這是字符串的存儲格式，后面跟著的7是字段time長度，后面七位是日期的存儲格式。在邏輯ROWID中，數值、字符和日期類型的存儲格式都和它們本身的存儲格式一致，這里不在贅述。
一般情況下，使用一位來表示長度，但是如果長度超過了127（16進制DUMP的結果是7F），則長度開始用兩位表示。第一位以8開頭，這個8只是標識位，表明長度字段現在由兩位來表示。例如長度128表示位8080，而支持的最大值3800表示為8ED8。
==============================================================
Oracle基本數據類型存儲格式淺析（五）——RAW類型
發表人:yangtingkun | 發表時間: 2004年十二月23日, 15:20
和其他數據類型相比，RAW類型的存儲顯得直觀多了，它和SELECT時數據展示的值完全一樣。（SELECT時是按照16進制展示的）
?
SQL> create table test_raw (id number, raw_date raw(10));
表已創建。
SQL> insert into test_raw values (1, hextoraw('ff'));
已創建 1 行。
SQL> drop table test_raw;
表已丟棄。
SQL> create table test_raw (raw_col raw(10));
表已創建。
SQL> insert into test_raw values (hextoraw('ff'));
已創建 1 行。
SQL> insert into test_raw values (hextoraw('0'));
已創建 1 行。
SQL> insert into test_raw values (hextoraw('23fc'));
已創建 1 行。
SQL> insert into test_raw values (hextoraw('fffffffffff'));
已創建 1 行。
SQL> insert into test_raw values (hextoraw('ffffffffffffffffffff'));
已創建 1 行。
SQL> insert into test_raw values (utl_raw.cast_to_raw('051'));
已創建 1 行。
SQL> select raw_col, dump(raw_col, 16) dump_raw from test_raw;
RAW_COL????????????? DUMP_RAW
-------------------- -----------------------------------------------
FF?????????????????? Typ=23 Len=1: ff
00?????????????????? Typ=23 Len=1: 0
23FC???????????????? Typ=23 Len=2: 23,fc
0FFFFFFFFFFF???????? Typ=23 Len=6: f,ff,ff,ff,ff,ff
FFFFFFFFFFFFFFFFFFFF Typ=23 Len=10: ff,ff,ff,ff,ff,ff,ff,ff,ff,ff
303531?????????????? Typ=23 Len=3: 30,35,31
已選擇6行。
RAW類型的存儲很簡單，對比字段的查詢結果和DUMP的結果就一目了然了。
需要注意的是，兩種轉化為RAW的函數之間的差別。當使用HEXTORAW時，會把字符串中數據當作16進制數。而使用UTL_RAW.CAST_TO_RAW時，直接把字符串中每個字符的ASCII碼存放到RAW類型的字段中。
SQL> insert into test_raw values ('gg');
insert into test_raw values ('gg')
???????????????????????????? *
ERROR 位于第 1 行:
ORA-01465: 無效的十六進制數字
SQL> insert into test_raw values (hextoraw('gg'));
insert into test_raw values (hextoraw('gg'))
????????????????????????????????????? *
ERROR 位于第 1 行:
ORA-01465: 無效的十六進制數字
SQL> insert into test_raw values (utl_raw.cast_to_raw('gg'));
已創建 1 行。
SQL> select raw_col, dump(raw_col, 16) dump_raw from test_raw;
RAW_COL????????????? DUMP_RAW
-------------------- ----------------------------------------------
FF?????????????????? Typ=23 Len=1: ff
00?????????????????? Typ=23 Len=1: 0
23FC???????????????? Typ=23 Len=2: 23,fc
6767???????????????? Typ=23 Len=2: 67,67
0FFFFFFFFFFF???????? Typ=23 Len=6: f,ff,ff,ff,ff,ff
FFFFFFFFFFFFFFFFFFFF Typ=23 Len=10: ff,ff,ff,ff,ff,ff,ff,ff,ff,ff
303531?????????????? Typ=23 Len=3: 30,35,31
已選擇7行。
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=607570