做测试时，用普通用户查看ORACLE执行计划时发现普通用户没有autotrace权限，报错如下：

SQL> set autotrace trace

SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

缺少plustrace权限，记录一下处理过程。

 

通过以下方法可以把Autotrace的权限授予Everyone，

如果你需要限制Autotrace权限，可以把对public的授权改为对特定user的授权。

 

SQL> conn / as sysdba

Connected.

 

SQL> @?/rdbms/admin/utlxplan

Table created.

 

SQL> create public synonym plan_table for plan_table;

create public synonym plan_table for plan_table

                      *

ERROR at line 1:

ORA-00955: name is already used by an existing object

 

SQL> grant all on plan_table to public;

Grant succeeded.

 

SQL> @?/sqlplus/admin/plustrce

SQL> drop role plustrace;

drop role plustrace

          *

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

 

SQL> create role plustrace;

Role created.

 

SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

 

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

 

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

 

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> set echo off

 

DBA用户首先被授予了plustrace角色,然后我们可以把plustrace授予public

这样所有用户都将拥有plustrace角色的权限.

 

SQL> grant plustrace to public;

Grant succeeded.

然后我们就可以使用AutoTrace的功能了.

 

 

 

关于Autotrace几个常用选项的说明:

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式

SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告

SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息

SET AUTOTRACE ON ----------------- 包含执行计划和统计信息

SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出

 

 

 

reference                            http://www.eygle.com/faq/AutoTrace.htm