SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
ISSUE:
SQL> set autotrace traceonly;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SOLUTION:
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
It also grants SELECT on V_$SESSTAT, V_$STATNME and V_$MYSTAT.
PLUSTRACE is granted to the DBA role with ADMIN OPTION.
For 9i and earlier 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 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
