[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

--//我自己還是無法解析看到的現象.