Oracle數據類型詳細解釋

jopen 12年前發布 | 47K 次閱讀 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，并沒有超過數據類型的限制。

主要描述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×10ⁿ，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 10¹²⁵ with up to 38 significant digits.

Negative numbers from -1 x 10^-130 to 9.99...99 x 10¹²⁵ 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……×10¹²⁵。

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。

負數和正數在各使用了一半的編碼，因此具有相同的極值范圍。

和其他數據類型相比，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行。

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的編碼規則，而且通過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。

本文對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;

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分鐘。

如果直接在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，似乎目前沒有使用。

這篇文章描述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行。

這篇文章描述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年。

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1384843345071.html

Oracle 數據庫服務器

Oracle數據類型詳細解釋

相關經驗

相關資訊

相關文檔

目錄