Doyensys Blog

I was trying few things in Oracle 18c database using command window and needed autotrace enable for the same.

When i had given command “set autotrace traceonly” it has thrown error as below,

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

SP2-0611: Error enabling STATISTICS report

It seems something wired here.

Few steps need to follow to work autotrace in sqlplus.

1. cd $oracle_home/rdbms/admin

2. log into sqlplus as sys or system

3. run SQL> @utlxplan

4. run SQL> create public synonym plan_table for plan_table

5. run SQL> grant all on plan_table to public

6. exit sqlplus and cd $oracle_home/sqlplus/admin

7. log into sqlplus as SYS

8. run SQL> @plustrce

9. run SQL> grant plustrace to public

Let’s follow another steps.

$ cd $ORACLE_HOME/sqlplus/admin/
$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 – Production on Wed Oct 17 12:40:27 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.3.0.0.0

SQL> alter session set container=testpdb1;
Session altered.

SQL> @plustrce
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>
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.

SQL>
SQL> set echo off
SQL>
SQL> grant plustrace to public;
Grant succeeded.

After following all above steps, now we are good with autotrace traceonly.

SQL> conn hr/hr@testpdb1
Connected.
SQL>
SQL> set autotrace traceonly
SQL>

Set Autotrace Traceonly giving error SP2-0618 and SP2-0611 in Oracle 18c