[20250225]11g存在TIMESTAMP(13) WITH TIME ZONE数据类型吗？.txt

--//在11g发现1个奇怪的问题，一些视图TIMESTAMP日期类型是TIMESTAMP(13) WITH TIME ZONE，我记忆里最大是9位小数点。
--//做一些测试：

1.环境：
SYS@book> @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

2.测试：
SYS@book> select owner,table_name,column_name,data_type,data_scale from dba_tab_cols where data_type like 'TIMESTAMP(13)%';
OWNER TABLE_NAME                     COLUMN_NAME                    DATA_TYPE                      DATA_SCALE
----- ------------------------------ ------------------------------ ------------------------------ ----------
SYS   GV_$INCMETER_CONFIG            MODIFICATION_TIME              TIMESTAMP(13) WITH TIME ZONE           13
SYS   GV_$INCMETER_INFO              CREATE_TIME                    TIMESTAMP(13) WITH TIME ZONE           13
SYS   GV_$INCMETER_SUMMARY           LATEST_INC_CTIME               TIMESTAMP(13) WITH TIME ZONE           13
SYS   GV_$INCMETER_SUMMARY           OLDEST_PERSISTENT_INC_CTIME    TIMESTAMP(13) WITH TIME ZONE           13
SYS   GV_$INCMETER_SUMMARY           OLDEST_TRANSIENT_INC_CTIME     TIMESTAMP(13) WITH TIME ZONE           13
SYS   GV_$INCMETER_SUMMARY           CREATE_TIME                    TIMESTAMP(13) WITH TIME ZONE           13
SYS   V_$INCMETER_CONFIG             MODIFICATION_TIME              TIMESTAMP(13) WITH TIME ZONE           13
SYS   V_$INCMETER_INFO               CREATE_TIME                    TIMESTAMP(13) WITH TIME ZONE           13
SYS   V_$INCMETER_SUMMARY            LATEST_INC_CTIME               TIMESTAMP(13) WITH TIME ZONE           13
SYS   V_$INCMETER_SUMMARY            OLDEST_PERSISTENT_INC_CTIME    TIMESTAMP(13) WITH TIME ZONE           13
SYS   V_$INCMETER_SUMMARY            OLDEST_TRANSIENT_INC_CTIME     TIMESTAMP(13) WITH TIME ZONE           13
SYS   V_$INCMETER_SUMMARY            CREATE_TIME                    TIMESTAMP(13) WITH TIME ZONE           13
12 rows selected.

--//简单建立表测试看看：
SCOTT@book> create table ttt1 (a TIMESTAMP(13) WITH TIME ZONE);
create table ttt1 (a TIMESTAMP(13) WITH TIME ZONE)
                               *
ERROR at line 1:
ORA-30088: datetime/interval precision is out of range


SCOTT@book> create table ttt1 (a TIMESTAMP(10) WITH TIME ZONE);
create table ttt1 (a TIMESTAMP(10) WITH TIME ZONE)
                               *
ERROR at line 1:
ORA-30088: datetime/interval precision is out of range

$ oerr ora 30088
30088, 00000, "datetime/interval precision is out of range"
// *Cause: The specified datetime/interval precision was not between 0 and 9.
// *Action: Use a value between 0 and 9 for datetime/interval precision.
--//根本不可能建立TIMESTAMP(13) WITH TIME ZONE的数据类型。

SCOTT@book> create table ttt1 (a TIMESTAMP(9) WITH TIME ZONE);
Table created.
--//最大支持9.

SYS@book> @ v2 gV$INCMETER_CONFIG
Show SQL text of views matching "gV$INCMETER_CONFIG"...
no rows selected

VIEW_NAME                      TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
GV$INCMETER_CONFIG             select inst_id,       tilt,       cfactor,        wfactor,        wtfactor,        mtime from
                               x$dbkincmetcfg

SYS@book> @ desc  x$dbkincmetcfg
           Name        Null?    Type
           ----------- -------- ----------------------------
    1      ADDR                 RAW(8)
    2      INDX                 NUMBER
    3      INST_ID              NUMBER
    4      ID                   NUMBER
    5      TILT                 NUMBER
    6      FLAGS                NUMBER
    7      CFACTOR              NUMBER
    8      WFACTOR              NUMBER
    9      WTFACTOR             NUMBER
   10      MTIME                TIMESTAMP(13) WITH TIME ZONE

--//在21c下不存在这个问题，明显11g下存在bug，不知道oracle在11g如何实现的。
SYS@book> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@book> select owner,table_name,column_name,data_type,data_scale from dba_tab_cols where data_type like 'TIMESTAMP(13)%';
no rows selected