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

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 – Production on Wed Oct 17 12:40:27 2018
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release – Production

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

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

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

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

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

  • February 20, 2019 | 44 views
  • Comments