ORA-01919: role ‘PLUSTRACE’ does not exist

The autotrace provides instantaneous feedback including the returned rows, execution plan, and statistics. The user doesn’t need to be concerned about trace file locations and formatting since the output is displayed instantly on the screen. This is very important data that can be used to tune the SQL statement.

Problem: 

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> GRANT PLUSTRACE TO scott;
GRANT PLUSTRACE TO scott
*
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE’ does not exist

solution:

Run plustrce.sql through SYS schema if it did not run before
<pre>
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
</pre>

Grant PLUSTRACE to <USER WHO WANTS TO RUN AUTOTRACE>

Also PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan . For creation of plan_table ,UTLXPLAN.sql have to be in user’s schema.

SQL> @/u01/app/oracle/product/11.1.0/db/sqlplus/admin/plustrce.sql
This script in turn will execute below sql statements:
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

For any specific SQL query you can see SQL execution performance statistics. In SQL*Plus you can issue the “set autotrace” command and then issue your SQL statement:

SQL> conn scott/scott
Connected.
SQL> set autot TRACE STAT
SQL> select * from t1;

14 rows selected.
Statistics
———————————————————-
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed