【ERROR】非DBA使用者要使用autotrace功能，報錯（SP2-0618:和SP2-0611:和ORA-01919）

lhrbest發表於2016-03-22
Error
一、問題描述
     使用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/26736162/viewspace-2061718/，如需轉載，請註明出處，否則將追究法律責任。

相關文章