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

SET AUTOTRACE TRACEONLY command may sometimes fail with SP2-0618 and SP2-0611
exceptions. Which needs to be resolved by creating PLUSTRACE role and assigning
it to USER.


C:\> sqlplus scoot/tiger

SQL*Plus: Release Production on Thu Dec 13 17:37:25 2012

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

Connected to:
Oracle Database 11g Release - Production

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


For execution AUTOTRACE the users needs to have the PLUSTRACE role, which does not
exist by default. PLUSTRACE role can be created using SYS user by executing

The plustrace.sql creates the PLUSTRACE role and grants SELECT on V_$SESSTAT,
V_$STATNME and V_$MYSTAT. PLUSTRACE is granted to the DBA role with ADMIN OPTION.

For 9i and eariler databases you may also need to create the plan table by
executing following script

The PLAN_TABLE already exists on database version 10g and higher.

SQL> connect sys/sys as sysdba

SQL> @%oracle_home%\sqlplus\admin\plustrce.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> 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.

After executing ORACLE_HOME\sqlplus\admin\plustrce.sql, we need to grant the
PLUSTRACE role to the user.

SQL> grant plustrace to scott;
Grant succeeded.

SQL> connect scott/tiger

SQL> set autotrace trace

SQL> select user from dual;
Execution Plan
Plan hash value: 1388734953
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |

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

NOTE: For UNIX/LINUX environments ORACLE_HOME should be written as $ORACLE_HOME
%oracle_home% as is this post is for windows environments.

Related Posts:
- Oracle SQL Developer - Autotrace - Insufficient Privileges
- Query Optimization Tips for Oracle
- DBMS_PROFILER: Overview and How to Install
- Oracle : TKPROF simple steps
- DBMS_STATS Gather Statistics of Schema, Tables, Indexes
- Oracle: Analyze Table or Index