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>
Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert
