【ERROR】非DBA用户要使用autotrace功能，报错（SP2-0618:和SP2-0611:和ORA-01919）

一、问题描述

     使用sqlplus时，连接到业务用户时，想查看某SQL的执行计划，报【SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled】和【SP2-0611: Error enabling STATISTICS report】，通过相关文档检索后得知原因是缺少plus trace ROLE的权限，解决思路：赋予用户plustrace 权限即可。 

二、操作过程

1.登录用户，打开autotrace功能时报错

[oracle@db2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 09:29:29 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn ptpcpuser

Enter password:

Connected.

SQL> set autot on

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

SP2-0611: Error enabling STATISTICS report

2.给用户赋予plustrace权限，报错【ORA-01919: role 'PLUSTRACE' does not exist】该角色不存在

SQL> conn / as sysdba

Connected.

SQL >grant plustrace to scott;

grant plustrace to scott

      *

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

3.安装plustrce包，建立plustrace角色，执行$ORACLE_HOME/sqlplus/admin/plustrce.sql包

SQL >conn / as sysdba

Connected.

SQL >@?/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 >

4.给用户赋予plustrace权限成功

SQL> grant plustrace to ptpcpuser;

Grant succeeded.

5.连接该用户，并且打开autotrace成功

SQL> conn ptpcpuser

Enter password:

Connected.

SQL> set autot trace

SQL> select nvl(SUM(t.bean_count), 0)/100 from bean_record t WHERE t.invest_multip > 1 AND t.status = '2';

Execution Plan

----------------------------------------------------------

Plan hash value: 3322369200

--------------------------------------------------------------------------------

--

| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time

 |

--------------------------------------------------------------------------------

--

|   0 | SELECT STATEMENT   |             |     1 |     9 | 77835   (1)| 00:15:35

 |

|   1 |  SORT AGGREGATE    |             |     1 |     9 |            |

 |

|*  2 |   TABLE ACCESS FULL| BEAN_RECORD |  4591K|    39M| 77835   (1)| 00:15:35

 |

--------------------------------------------------------------------------------

--

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("T"."INVEST_MULTIP">1 AND "T"."STATUS"='2')

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

     313283  consistent gets

     313277  physical reads

          0  redo size

        549  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>

三、总结

     此问题有两个知识点，（1）是用户缺少set autotrace的权限，（2）是数据库缺少角色plustrace。这个问题对DBA来讲不是什么难事，但是还是要注意权限的管理与回收。继续学习喽，下回见。

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

转载于:http://blog.itpub.net/26148431/viewspace-2061221/