[20220324]toad与sql profile使用问题.txt

[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导致这样的情况出现。