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

If autotrace is not working for an user for an sql query , then you have to grant permissions to further use it

kish@exdbx<>conn test
Enter password:
Connected.
kish@exdbx<>set autot traceonly
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

PLUSTRACE is a role for which can be assigned to user to perform autotrace activity for an SQL. In oracle home , you can find a script to called plustrce.sql which can be invoked directly to create plustrace role

kish@exdbx<>!echo $ORACLE_HOME
/data/oracle/product/11.2.0/db

kish@exdbx<>@/data/oracle/product/11.2.0/db/sqlplus/admin/plustrce.sql
kish@exdbx<>
kish@exdbx<>drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist


Elapsed: 00:00:00.04
kish@exdbx<>create role plustrace;

Role created.

Elapsed: 00:00:00.34
kish@exdbx<>
kish@exdbx<>grant select on v_$sesstat to plustrace;

Grant succeeded.

Elapsed: 00:00:00.11
kish@exdbx<>grant select on v_$statname to plustrace;

Grant succeeded.

Elapsed: 00:00:00.02
kish@exdbx<>grant select on v_$mystat to plustrace;

Grant succeeded.

Elapsed: 00:00:00.05
kish@exdbx<>grant plustrace to dba with admin option;

Grant succeeded.

Elapsed: 00:00:00.14
kish@exdbx<>
kish@exdbx<>set echo off

Grant plustrace role to user ‘test’

kish@exdbx<>grant plustrace to test;

Grant succeeded.

Elapsed: 00:00:00.02

Now give a try

kish@exdbx<>conn test
Enter password:
Connected.
kish@exdbx<>set autot traceonly
kish@exdbx<>select order_id,order_priority from tabx where order_id < 1000;

524787 rows selected.

Elapsed: 00:00:08.11

Execution Plan
----------------------------------------------------------
Plan hash value: 385092089

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |   514K|  3515K|  3271   (1)| 00:00:40 |
|*  1 |  TABLE ACCESS STORAGE FULL| TABX |   514K|  3515K|  3271   (1)| 00:00:40 |
----------------------------------------------------------------------------------

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

   1 - storage("ORDER_ID"<1000)
       filter("ORDER_ID"<1000)


Statistics
----------------------------------------------------------
         39  recursive calls
          0  db block gets
      11945  consistent gets
      11880  physical reads
          0  redo size
    9817518  bytes sent via SQL*Net to client
     385358  bytes received via SQL*Net from client
      34987  SQL*Net roundtrips to/from client
         13  sorts (memory)
          0  sorts (disk)
     524787  rows processed

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s