[20230405]奇怪的显示输出宽度.txt

[20230405]奇怪的显示输出宽度.txt --//放假前遇到的问题,我想看看asm实例出现那些等待事件. SYS@my_asm> @ver BANNER ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SYS@my_asm> @ ash/ashtop3 event 1=1 &day     Total                                                                                                                                            Distinct Distinct   Seconds     AAS %This   EVENT                                      FIRST_SEEN                             LAST_SEEN                              Execs Seen  Tstamps --------- ------- ------- ------------------------------------------ -------------------------------------- -------------------------------------- ---------- --------      2020      .0   83% |                                            2023-04-05 08:46:48                    2023-04-06 08:45:23                            11     2000       128      .0    5% | rdbms ipc reply                            2023-04-05 08:49:42                    2023-04-06 08:37:34                           128      128        72      .0    3% | GPnP Initialization                        2023-04-05 08:49:42                    2023-04-06 07:56:39                             1       72        38      .0    2% | CSS operation: data update                 2023-04-05 10:18:13                    2023-04-06 08:04:41                             1       38        38      .0    2% | GPnP Termination                           2023-04-05 10:01:19                    2023-04-06 08:37:34                             1       38        36      .0    1% | enq: RB - contention                       2023-04-05 09:13:09                    2023-04-06 08:38:08                             1       36        23      .0    1% | ASM file metadata operation                2023-04-05 13:20:48                    2023-04-06 03:31:05                             1        8 --//我发现一个奇怪现象就是FIRST_SEEN,LAST_SEEN显示宽度超长,感觉有点奇怪!! SYS@my_asm> select sysdate,to_char(sysdate) from dual ; SYSDATE             TO_CHAR(SYSDATE) ------------------- -------------------------------------- 2023-04-06 08:49:29 2023-04-06 08:49:29 SYS@my_asm> select sysdate,to_char(sysdate,'yyyy')  from dual ; SYSDATE             TO_CHAR( ------------------- -------- 2023-04-06 08:50:48 2023 --//可以发现TO_CHAR(SYSDATE)的显示宽度39,而显示字符19个.to_char(sysdate,'yyyy')类似,为什么呢? --//我以前遇到的情况就是cursor_sharing=force会出现这样的情况.我看了一下asm实例不支持cursor_sharing参数. --//也就是不可能是参数cursor_sharing=force引起的情况. --//asm实例非常特殊,仅仅启动到nomount状态. SYS@my_asm> select * from v$database; select * from v$database               * ERROR at line 1: ORA-01507: database not mounted --//我在我的测试环境尝试看看,启动到nomount状态看看: SYS@book> startup  nomount ORACLE instance started. Total System Global Area  643084288 bytes Fixed Size                  2255872 bytes Variable Size             205521920 bytes Database Buffers          427819008 bytes Redo Buffers                7487488 bytes SYS@book> select sysdate,to_char(sysdate,'yyyy')  from dual ; SYSDATE             TO_CHAR( ------------------- -------- 2023-04-06 08:52:50 2023 --//可以发现我再现了asm实例看到的情况. --//我检查NLS*环境变量,发现定义如下: $ env | grep -i nls NLS_LANG=AMERICAN_AMERICA.ZHS16GBK NLS_TIME_TZ_FORMATx=HH24.MI.SSXFF TZH:TZM NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS $ export NLS_LANG=AMERICAN_AMERICA.US7ascii SYS@book> select sysdate,to_char(sysdate,'yyyy')  from dual ; SYSDATE             TO_C ------------------- ---- 2023-04-06 09:02:50 2023 --//这样显示宽度才是正常.可以这样假设oracle启动到nomount状态实际上字符集是us7ascii. --//继续: $ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK --//再次登录: SYS@book> select sysdate,to_char(sysdate,'yyyy')  from dual ; SYSDATE             TO_CHAR( ------------------- -------- 2023-04-06 09:07:46 2023 SYS@book> alter database mount ; Database altered. SYS@book> select sysdate,to_char(sysdate,'yyyy')  from dual ; SYSDATE             TO_C ------------------- ---- 2023-04-06 09:07:57 2023 --//到了mount状态读取了控制文件才知道数据库采用的字符集. --//另外让我想起以前的一个测试:http://blog.itpub.net/267265/viewspace-2561490/=>[20190116]诡异的问题2.txt --//如果使用12c以上的sqlplus客户端以sys用户连接10g,11g数据库,会出现一个独特的现象,第一次执行显示sysdate不正常. --//注意检查不要存在login.sql,glogin.sql是否有执行语句. SYS@192.168.100.78:1521/book> show sqlpluscompatibility sqlpluscompatibility 19.0.0 SYS@192.168.100.78:1521/book> select sysdate,to_char(sysdate) from dual; SYSDATE      TO_CHAR(SYSDATE) ------------ ------------------- 2023-04-06 0 2023-04-06 09:21:44 SYS@192.168.100.78:1521/book> select sysdate,to_char(sysdate) from dual; SYSDATE             TO_CHAR(SYSDATE) ------------------- ------------------- 2023-04-06 09:22:02 2023-04-06 09:22:02 --//我自己还是无法解析看到的现象.