ORACLE普通用户开启AUTOTRACE功能

需要做执行计划分析，新服务器上执行autotrace提示要确认PLUSTRACE角色是否存在

SQL> connect osa_test/osa_test
Connected.
SQL> set autotrace traceonly
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

尝试给用户赋予权限：

SQL> connect / as sysdba
Connected.

SQL> grant PLUSTRACE to osa_test;
grant PLUSTRACE to osa_test
      *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

提示角色不存在
google后找到解决方法，执行

@$ORACLE_HOME/sqlplus/admin/plustrce.sql脚本创建角色

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
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>
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>
SQL> set echo off
SQL> 

执行后创建了PLUSTRACE这个角色

需要使用TRACE功能的用户需要授予这个角色即可

SQL> select * from dba_roles where role='PLUSTRACE';

ROLE                           PASSWORD
------------------------------ --------
PLUSTRACE                      NO

SQL> connect osa_test/osa_test
Connected.
SQL> set autotrace traceonly
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> connect / as sysdba
Connected.
SQL> grant PLUSTRACE to osa_test;

Grant succeeded.

SQL> connect osa_test/osa_test
Connected.

SQL> set autotrace traceonly
SQL> 

 

转载于:https://www.cnblogs.com/cycsa/archive/2013/05/22/3093290.html