Oracle Distilled

Problem:

You attempt to set autotrace and receive the following error message.

SQL> set autotrace traceonly;
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> connect / as sysdba

Solution:

The user executing AUTOTRACE needs to have the PLUSTRACE role. By default the PLUSTRACE role does not exist and can be created by the SYS user with ORACLE_HOME/sqlplus/admin/plustrace.sql.

The plustrace.sql creates the PLUSTRACE role and grants SELECT on V_$SESSTAT, V_$STATNME and V_$MYSTAT. PLUSTRACE is granted to the DBA role with ADMIN OPTION.

For databases earlier than 10g you might also need to create the plan table. Execute the script ORACLE_HOME/rdbms/admin/utlxplan.sql to create the PLAN_TABLE. The PLAN_TABLE already exists on database version 10g and higher.