Oracle常用数据类型存储格式解析

char and varchar2 数据类型（typ 96 and 1）

drop table test_char purge;   
create table test_char(c1 char(20),c2 varchar2(20)) tablespace BBED_TEST;
insert into test_char values (' zhang san ',' zhang san ');
insert into test_char values ('南昌','南昌');
commit;

col c1 for a20
col c2 for a20
col c1_hex for a80
col c2_hex for a60
select c1,c2,dump(c1,16) c1_hex,dump(c2,16) c2_hex from test_char;
   
C1                   C2                   C1_HEX                                                                           C2_HEX
-------------------- -------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------
 zhang san            zhang san           Typ=96 Len=20: 20,7a,68,61,6e,67,20,73,61,6e,20,20,20,20,20,20,20,20,20,20       Typ=1 Len=11: 20,7a,68,61,6e,67,20,73,61,6e,20
南昌                 南昌                 Typ=96 Len=20: e5,8d,97,e6,98,8c,20,20,20,20,20,20,20,20,20,20,20,20,20,20       Typ=1 Len=6: e5,8d,97,e6,98,8c


char类型，固定长度不足则用空格0x20填充到指定长度,最大长度2000
varchar2类型，变长长度,最大4000,12c开始可以支持32k,需要修改max_string_size参数

[root@cesdb3 ~]# echo -n "20,7a,68,61,6e,67,20,73,61,6e,20" | tr -d ',' | xxd -r -p ;echo 
 zhang san 
[root@cesdb3 ~]# echo -n "e5,8d,97,e6,98,8c" | tr -d ',' | xxd -r -p ;echo 
南昌
[root@cesdb3 ~]# echo -n " zhang san " | xxd -p
207a68616e672073616e20
[root@cesdb3 ~]# echo -n "南昌" | xxd -p
e58d97e6988c

简易字符和16进制对照表

在Liunx上可以使用man ascii查看16进制对应表，以下有个简易的对照表
   Tables
       For convenience, let us give more compact tables in hex and decimal.

          2 3 4 5 6 7       30 40 50 60 70 80 90 100 110 120
        -------------      ---------------------------------
       0:   0 @ P ` p     0:    (  2  <  F  P  Z  d   n   x
       1: ! 1 A Q a q     1:    )  3  =  G  Q  [  e   o   y
       2: " 2 B R b r     2:    *  4  >  H  R  \  f   p   z
       3: # 3 C S c s     3: !  +  5  ?  I  S  ]  g   q   {
       4: $ 4 D T d t     4: "  ,  6  @  J  T  ^  h   r   |
       5: % 5 E U e u     5: #  -  7  A  K  U  _  i   s   }
       6: & 6 F V f v     6: $  .  8  B  L  V  `  j   t   ~
       7: ´ 7 G W g w     7: %  /  9  C  M  W  a  k   u  DEL
       8: ( 8 H X h x     8: &  0  :  D  N  X  b  l   v
       9: ) 9 I Y i y     9: ´  1  ;  E  O  Y  c  m   w
       A: * : J Z j z
       B: + ; K [ k {
       C: , < L \ l |
       D: - = M ] m }
       E: . > N ^ n ~
       F: / ? O _ o DEL

number数据类型(typ 2)

create table test_number(id number) tablespace BBED_TEST;
insert into test_number values (123456.789);
insert into test_number values (-123456.789);
commit;

col id for 999999.999
col id_dec for a40
col id_hex for a40
select id,dump(id,10) id_dec,dump(id,16) id_hex from test_number;

SQL> select id,dump(id,10) id_dec,dump(id,16) id_hex from test_number;

         ID ID_DEC                                   ID_HEX
----------- ---------------------------------------- ----------------------------------------
 123456.789 Typ=2 Len=6: 195,13,35,57,79,91          Typ=2 Len=6: c3,d,23,39,4f,5b
-123456.789 Typ=2 Len=7: 60,89,67,45,23,11,102       Typ=2 Len=7: 3c,59,43,2d,17,b,66


number 类型，是一个可变长度的数组，有以下3部分组成
<[length]>,sign bit/exponent,digit1,digit2,...,digit20
长度        符号位/指数位       数字1，数字2，数字3

sign bit/exponent用一个字节表示，有以下含义
如果 first byte >= 128,则为正数，指数为(first byt - 193)=(first byte - 128 - 65)
如果 first byte <  128,则为负数，指数为(62 - first byte)=(255 - first byte) - 128 - 65

数字的话，以100为基数的科学计数法
正数每个数字为当前值-1
负数每个数字为101-当前值，负数在最后添加一个数据位102(0x66)用于排序,这个数据位可忽略


分析 123456.789
第一个字节0xc3=195(dec)>128为正数，指数为195-193=2
0x0d=13(dec) : 13-1=12 > 12 * 100^2  = 120000
0x23=35(dec) : 35-1=34 > 34 * 100^1  =   3400
0x39=57(dec) : 57-1=56 > 56 * 100^0  =     56
0x4f=79(dec) : 79-1=78 > 78 * 100^-1 =       .78
0x5b=91(dec) : 91-1=90 > 90 * 100^-2 =       .009
                                sum  = 123456.789

分析 -123456.789         
第一个字节0x3c=60(dec)<128为负数，指数为62-60=2
0x59=89(dec) : 101-89=12 > 12 * 100^2  = 120000
0x43=67(dec) : 101-67=34 > 34 * 100^1  =   3400
0x2d=45(dec) : 101-45=56 > 56 * 100^0  =     56
0x17=23(dec) : 101-23=78 > 78 * 100^-1 =       .78
0x0b=11(dec) : 101-11=90 > 90 * 100^-2 =       .009     
0x66=102(dec) 忽略
                                  sum  = 123456.789(-)

1到100十进制和16进制对应关系

  Dec    Hex   Dec   Hex   Dec   Hex   Dec   Hex   Dec   Hex   
──────────────────────────────────────────────────────────────────
   1     01    21    15    41    29    61    3D    81    51   
   2     02    22    16    42    2A    62    3E    82    52  
   3     03    23    17    43    2B    63    3F    83    53  
   4     04    24    18    44    2C    64    40    84    54  
   5     05    25    19    45    2D    65    41    85    55  
   6     06    26    1A    46    2E    66    42    86    56  
   7     07    27    1B    47    2F    67    43    87    57  
   8     08    28    1C    48    30    68    44    88    58  
   9     09    29    1D    49    31    69    45    89    59  
   10    0A    30    1E    50    32    70    46    90    5A  
   11    0B    31    1F    51    33    71    47    91    5B  
   12    0C    32    20    52    34    72    48    92    5C  
   13    0D    33    21    53    35    73    49    93    5D  
   14    0E    34    22    54    36    74    4A    94    5E  
   15    0F    35    23    55    37    75    4B    95    5F  
   16    10    36    24    56    38    76    4C    96    60  
   17    11    37    25    57    39    77    4D    97    61  
   18    12    38    26    58    3A    78    4E    98    62  
   19    13    39    27    59    3B    79    4F    99    63  
   20    14    40    28    60    3C    80    50    100   64  

date数据类型（typ 12）

date类型使用7个字节存储

create table test_date(t1 date);
insert into test_date values (to_date('2025-01-23 14:15:59','yyyy-mm-dd hh24:mi:ss'));
commit;   
   
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col t1 for a20
col t1_dec for a40
col t1_hex for a40
select t1,dump(t1,10) t1_dec,dump(t1,16) t1_hex from test_date;
   
T1                   T1_DEC                                   T1_HEX
-------------------- ---------------------------------------- ----------------------------------------
2025-01-23 14:15:59  Typ=12 Len=7: 120,125,1,23,15,16,60      Typ=12 Len=7: 78,7d,1,17,f,10,3c

转换方式
1.先转成10进制
2.世纪和年-100
3.时分秒-1

世纪: 120-100=20
年份: 125-100=25
月:         1=01
日:        23=23
时:      15-1=14
分:      16-1=15
秒:      60-1=59
sum           2025-01-23 14:15:59

[root@cesdb3 ~]# echo "78,7d,1,17,f,10,3c" | tr ',' '\n' | while read -r hex; do printf "%d " "0x$hex"; done; echo
120 125 1 23 15 16 60
[root@cesdb3 ~]# echo "120 125 1 23 15 16 60" | tr ' ' '\n' | while read -r dec; do printf "%02x" "$dec"; done; echo 
787d01170f103c

timestamp数据类型（typ 180）

timestamp类型使用11个字节存储，秒后面的精度为9位

alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff';   
create table test_timestamp(t1 timestamp);
insert into test_timestamp values (to_timestamp('2025-01-23 14:15:59.123456','yyyy-mm-dd hh24:mi:ss.ff'));
commit;    
   
col t1 for a30
col t1_dec for a50
col t1_hex for a60
select t1,dump(t1,10) t1_dec,dump(t1,16) t1_hex from test_timestamp;  
   

T1                             T1_DEC                                             T1_HEX
------------------------------ -------------------------------------------------- ------------------------------------------------------------
2025-01-23 14:15:59.123456     Typ=180 Len=11: 120,125,1,23,15,16,60,7,91,202,0   Typ=180 Len=11: 78,7d,1,17,f,10,3c,7,5b,ca,0   


可以看到前7个字节和data类型一样存储
后面4个字节合并成一个16进制数据再转成10进制

[root@cesdb3 ~]# printf "%d\n" "0x75BCA00"
123456000

[root@cesdb3 ~]# echo "7,5b,ca,0" | awk -F',' '{for(i=1;i<=NF;i++) {if(i==4 && $i=="0") $i="00"; printf("%02s", $i)}}' | tr -d ' ' | xargs -I {} printf "%d\n" "0x{}"
123456000