:

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

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

This error was produced when logging in as a specific user  from a certain SQLPlus client version. To give you an example, when logging in  as USERA with a 9.2.0.1.0 client, the above error was produced. If I logged in  as a higher privileged user, SYS for example, the error would not be produced!

Logging into the same database as the same user from a  9.2.0.8.0 client did not produce the error, and the SET AUTOTRACE TRACEONLY  command worked fine. The same was true from a 10G client.

Looking into this matter further showed that the set-up of the PLUSTRACE role, which can be found in the  $ORACLE_HOME\sqlplus\admin\plustrce.sql script, shows that the role is created and that select on three v$ views is granted to the role. Here is the output from the script:

drop role plustrace;
create role plustrace;

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$session to plustrace;

I checked that USERA had the PLUSTRACE role, which was all correct. So I decided to explicitly grant select on these views to USERA. And guess what? It worked…

To conclude, it seems that having a 9.2.0.1.0 client connecting to a 10g database may affect the grants the user is able to access through a role. The simple solution is to always connect to a 10g database with a 10g client and this should prevent the problem. However, in a company where many PCs already have the 9i client installed it’s not so easy to get this done when upgrading to a new Oracle version.

This could also be the case for other client versions, such as the Oracle 11g client. I haven’t tested it out but in general it’s advisable to connect using the same version client wherever possible. Also, if you are using Windows make sure that the path for the home you want to use is listed first in the PATH environment variable.

Like it, share it...

Category: SQL Plus


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *