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

今天是2013-09-17，在今天学习sql执行计划的是发现如下问题：

问题描述：

在rhys用户下开启sql语句计划报如下错误：

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

问题分析：

由于该rhys账户没有PLUSTRACE角色导致此问题的产生。

解决办法：

1）查看$ORACLE_HOME/sqlplus/admin下的plustrce.sql发现如下内容：
-- Copyright (c) Oracle Corporation 1995, 2002. All Rights Reserved.
--
-- NAME
-- plustrce.sql
--
-- DESCRIPTION
-- Creates a role with access to Dynamic Performance Tables
-- for the SQL*Plus SET AUTOTRACE ... STATISTICS command.
-- After this script has been run, each user requiring access to
-- the AUTOTRACE feature should be granted the PLUSTRACE role by
-- the DBA.
--
-- USAGE
-- sqlplus "sys/knl_test7 as sysdba" @plustrce
--
-- Catalog.sql must have been run before this file is run.
-- This file must be run while connected to a DBA schema.

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
2）执行该sql创建plustrace角色。
SQL> @./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> 
SQL> grant plustrace to rhys;
Grant succeeded.

3）登录rhys账户在此开启sql语句分析功能。

SQL> conn rhys/amy
Connected.
SQL> set autotrace on 
SQL>

发现该问题得到解决。

SQL> r
  1  select ename,job,sal,dname from amy_emp,amy_dept where amy_emp.deptno=amy_dept.deptno
  2  and not exists
  3* (select * from amy_salgrade where amy_emp.sal between losal and hisal)

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2345751609

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     1 |    42 |     9  (23)| 00:00:01 |
|   1 |  NESTED LOOPS                |               |     1 |    42 |     9  (23)| 00:00:01 |
|   2 |   NESTED LOOPS               |               |     1 |    42 |     9  (23)| 00:00:01 |
|   3 |    MERGE JOIN ANTI           |               |     1 |    29 |     8  (25)| 00:00:01 |
|   4 |     SORT JOIN                |               |    14 |   294 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL       | AMY_EMP       |    14 |   294 |     3   (0)| 00:00:01 |
|*  6 |     FILTER                   |               |       |       |            |          |
|*  7 |      SORT JOIN               |               |     5 |    40 |     4  (25)| 00:00:01 |
|   8 |       TABLE ACCESS FULL      | AMY_SALGRADE  |     5 |    40 |     3   (0)| 00:00:01 |
|*  9 |    INDEX RANGE SCAN          | REVERSE_INDEX |     1 |       |     0   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID| AMY_DEPT      |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("AMY_EMP"."SAL"<="HISAL")
   7 - access(INTERNAL_FUNCTION("AMY_EMP"."SAL")>=INTERNAL_FUNCTION("LOSAL"))
       filter(INTERNAL_FUNCTION("AMY_EMP"."SAL")>=INTERNAL_FUNCTION("LOSAL"))
   9 - access("AMY_EMP"."DEPTNO"="AMY_DEPT"."DEPTNO")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        530  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>