[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

--//我自己还是无法解析看到的现象.