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

联系:手机/微信(+86 17813235971) QQ(107644445)

标题: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


可以打上对应Patch 26764561: ORA-00933 IN SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION