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 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>

  • February 20, 2019 | 44 views
  • Comments