[20220324]toad與sql profile使用問題.txt

--//今天在toad下使用oracle tunning advisor(OEM)模組管理sql proflie，進入介面報錯，彈出
''2022/01/25 11:15:44.861090'' is not a valid date and time.

--//我使用toad版本12.6.0.53，僅僅顯示1條，並且修改刪除的按鈕為灰色不能使用。

1.環境：
> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

--//跟蹤發現執行的是如下語句：
select * from sys.dba_sql_profiles order by created desc;

--//我發現CREATED 型別是timestamp型別。
> @desc sys.dba_sql_profiles
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      NAME                            NOT NULL VARCHAR2(128)
    2      CATEGORY                        NOT NULL VARCHAR2(128)
    3      SIGNATURE                       NOT NULL NUMBER
    4      SQL_TEXT                        NOT NULL CLOB
    5      CREATED                         NOT NULL TIMESTAMP(6)
    6      LAST_MODIFIED                            TIMESTAMP(6)
    7      DESCRIPTION                              VARCHAR2(500)
    8      TYPE                                     VARCHAR2(7)
    9      STATUS                                   VARCHAR2(8)
   10      FORCE_MATCHING                           VARCHAR2(3)
   11      TASK_ID                                  NUMBER
   12      TASK_EXEC_NAME                           VARCHAR2(128)
   13      TASK_OBJ_ID                              NUMBER
   14      TASK_FND_ID                              NUMBER
   15      TASK_REC_ID                              NUMBER
   16      TASK_CON_DBID                            NUMBER

--//對比11g，19c的情況，我發現11g下時間記錄都是像這樣 2021/12/10 15:09:04.000000，也就是秒後面的時間都是000000。
--//估計刪除秒後的時間部分就可以透過。
--//查詢檢視定義如下：

CREATE OR REPLACE FORCE VIEW SYS.DBA_SQL_PROFILES
(NAME, CATEGORY, SIGNATURE, SQL_TEXT, CREATED,
LAST_MODIFIED, DESCRIPTION, TYPE, STATUS, FORCE_MATCHING,
TASK_ID, TASK_EXEC_NAME, TASK_OBJ_ID, TASK_FND_ID, TASK_REC_ID,
TASK_CON_DBID)
BEQUEATH DEFINER
AS

SELECT so.name
      ,so.category
      ,so.signature
      ,st.sql_text
      ,ad.created
      ,ad.last_modified
      ,ad.description
      ,DECODE (ad.origin, 1, 'MANUAL', 2, 'AUTO', 'UNKNOWN')
      ,DECODE (BITAND (so.flags, 1), 1, 'ENABLED', 'DISABLED')
      ,DECODE (BITAND (sq.flags, 1), 1, 'YES', 'NO')
      ,ad.task_id
      ,ad.task_exec_name
      ,ad.task_obj_id
      ,ad.task_fnd_id
      ,ad.task_rec_id
      ,ad.task_con_dbid
FROM sqlobj$ so
      ,sqlobj$auxdata ad
      ,sql$text st
      ,sql$ sq
WHERE     so.signature = st.signature
       AND so.signature = ad.signature
       AND so.category = ad.category
       AND so.signature = sq.signature
       AND so.obj_type = 1
       AND ad.obj_type = 1;

--//很容易確定create,LAST_MODIFIED來之sqlobj$auxdata.
> select * from sqlobj$auxdata where SIGNATURE=12041055251246986088
2 @pr
==============================
SIGNATURE                     : 12041055251246986088
CATEGORY                      : DEFAULT
OBJ_TYPE                      : 1
PLAN_ID                       : 0
DESCRIPTION                   : switch ad21u3bw0dm4q => 0v9fdcvttdph1
CREATOR                       : SYS
ORIGIN                        : 1
VERSION                       : 19.0.0.0.0
CREATED                       : 2022-01-25 11:15:44.861090
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
LAST_MODIFIED                 : 2022-01-25 11:15:44.000000
LAST_VERIFIED                 :
PARSE_CPU_TIME                :
OPTIMIZER_COST                :
MODULE                        :
ACTION                        :
PRIORITY                      :
OPTIMIZER_ENV                 :
BIND_DATA                     :
PARSING_SCHEMA_NAME           :
EXECUTIONS                    :
ELAPSED_TIME                  :
CPU_TIME                      :
BUFFER_GETS                   :
DISK_READS                    :
DIRECT_WRITES                 :
ROWS_PROCESSED                :
FETCHES                       :
END_OF_FETCH_COUNT            :
TASK_ID                       :
TASK_EXEC_NAME                :
TASK_OBJ_ID                   :
TASK_FND_ID                   :
TASK_REC_ID                   :
FLAGS                         : 0
SPARE1                        :
SPARE2                        :
TASK_CON_DBID                 :
PL/SQL procedure successfully completed.
--//注意看下劃線的時間，LAST_MODIFIED秒後都是000000。

> select to_date(created,'yyyy-mm-dd hh24:mi:ss') from sqlobj$auxdata;
select to_date(created,'yyyy-mm-dd hh24:mi:ss') from sqlobj$auxdata
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

$ oerr ora 01830
01830, 00000, "date format picture ends before converting entire input string"
// *Cause:
// *Action:
--//還不能直接使用to_date函式完成轉換。直接使用to_char,注意這樣寫很不正規，當然定義日期格式要求就可以透過。

$ 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

> select created from sqlobj$auxdata;
CREATED
---------------------------------------------------------------------------
2022-01-07 11:24:18.933500
2022-01-25 10:04:27.421745
2022-01-25 10:17:25.932051
2021-12-30 10:35:10.843463
2022-01-24 16:21:07.740575
2022-01-25 11:15:44.861090
6 rows selected.

> update (select * from sqlobj$auxdata) set created=to_char(created,'yyyy-mm-dd hh24:mi:ss');
6 rows updated.

> select created from sqlobj$auxdata;
CREATED
---------------------------------------------------------------------------
2022-01-07 11:24:18.000000
2022-01-25 10:04:27.000000
2022-01-25 10:17:25.000000
2021-12-30 10:35:10.000000
2022-01-24 16:21:07.000000
2022-01-25 11:15:44.000000
6 rows selected.

> commit ;
Commit complete.

--//在toad相應介面下重新整理，沒有報錯，按鈕顏色也回覆正常，進入後可以發現created僅僅顯示到秒。
--//估計是toad的bug導致這樣的情況出現。

[20220324]toad與sql profile使用問題.txt

相關文章