[20130121]访问v$sql_plan的出现ora-07445问题.txt

SQL> select * from v$version where rownum<=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi


我想检查一个索引执行计划是否使用。
select * from v$sql_plan where object_owner='AAA' and object_name='I_XXXX';

出现如下提示：
ORA-03113: 通信通道的文件结束
进程 ID: 0
会话 ID: 2285 序列号: 2851

查看alert.log文件，发现:

Errors in file /u01/app/oracle/admin/xxx430/udump/xxx430_ora_3621.trc:
ORA-07445: exception encountered: core dump [msqopnws()+52] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Mon Jan 21 10:47:09 2013


google:V$SQL_PLAN ORA-03113 ora-07445

http://momendba.blogspot.com/2009/04/ora-07445-when-querying-vsqlplan-view.html

Workaround does not crash but "some information will be missing":

ORA-07445 [MSQSUB()+32] When Select From V$SQL_PLAN [ID 791225.1]

Bug 7022234 is Fixed in 11.2
Patches exist for it on most platforms on top of 10.2.0.4 at the moment of writing this note

1- apply the patch 7022234 if available
2- If the patch is not available you may use the following workaround
alter session set "_cursor_plan_unparse_enabled"=false;

-- 按照建议执行如下，可以正常访问。

alter session set "_cursor_plan_unparse_enabled"=false;