[20130121]訪問v$sql_plan的出現ora-07445問題.txt

[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;