Daily Archives: 2019 年 08 月 14 日

DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库版本

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                                0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

alert 日志报错

2019-08-14T11:30:15.112151+08:00
WARNING: too many parse errors, count=546 SQL hash=0x750004bb
PARSE ERROR: ospid=11550, error=933 for statement:
2019-08-14T11:30:15.112224+08:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x16a3e1db8 phd=0x1699bf628 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2019-08-14T11:30:15.114628+08:00
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xd0ba9890       259  type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x870ac548      2134  package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x870ac548      7342  package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0xc91e5518         1  anonymous block
WARNING: too many parse errors, count=646 SQL hash=0x750004bb
PARSE ERROR: ospid=11550, error=933 for statement:
2019-08-14T11:30:15.298603+08:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x16a3e1db8 phd=0x1699bf628 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2019-08-14T11:30:15.298698+08:00
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xd0ba9890       259  type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x870ac548      2134  package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x870ac548      7342  package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0xc91e5518         1  anonymous block

这里比较明显由于DELETE FROM wri$_adv_sqlt_rtn_planWHERE这条sql语法不对，导致无法解析因此报了ORA-00933错误,通过人工执行

SQL>  exec SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS();

PL/SQL procedure successfully completed.

后台alert日志重现该错误，证明该程序本身有问题，属于oracle bug范畴，查询mos发现相关Bug 26764561 : ORA-00933 IN SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
26764561