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 scoot/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.




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