处理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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-671201/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-671201/