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.

PROBLEM:

C:\> sqlplus scott/tiger

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

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

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

SOLUTION:

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 
ORACLE_HOME\sqlplus\admin\plustrce.sql 

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 
ORACLE_HOME\rdbms\admin\utlxplan.sql 

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

SQL> connect sys/sys as sysdba
Connected.

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> 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 Connected. 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 | ----------------------------------------------------------------- Statistics ---------------------------------------------------------- 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.