處理set autotrace故障又一例_ora-942_sp2-0611

SQL> set autot traceonly exp stat --啟用autotrace報如下錯誤
Error ORA-942 while gathering statistics
SP2-0611: Error enabling STATISTICS report
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
-bash-3.2$ oerr ora 942
00942, 00000, "table or view does not exist"
// *Cause:
// *Action:
-bash-3.2$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 17 11:42:08 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
-bash-3.2$ oerr sp2 0611 --用oerr查sp2-0611錯誤
00611, 0, "Error enabling %s report\n"
// *Document: NO
// *Cause:  Continuation of 613 or 618.
// *Action:
-bash-3.2$ oerr sp2 0613  --根據以上提示查sp2 0613
00613, 0, "Unable to verify PLAN_TABLE format or existence\n"
// *Cause:  An AUTOTRACE command was issued by a user with insufficient  --根據提示：檢查scott使用者是否存在plan_table表及是否被授予角色plustrace(此角色指令碼位於$ORACLE_HOME/sqlplus/admin/plustrce.sql)
//          privileges, or who did not have a PLAN_TABLE.
// *Action: Make sure the user has been granted the PLUSTRACE role,
//          and that a PLAN_TABLE has been created for the user.
-bash-3.2$

備記下plustrce.sql指令碼內容

set echo on

drop role plustrace;
create role plustrace;

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;

set echo off

最後再次執行set autot trace exp stat --成功ok