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

[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