role 'PLUSTRACE' does not exist

I have created a new user named watson and granted the related priviledges as following:

SQL> create user watson identified by watson;

SQL> grant resource ,connect,create session to watson;

There will be an error happened when we use this new user to trace the execution plan.The following is the prompt hinted by oracle database.

SQL> grant plustrace to watson;
grant plustrace to watson
      *
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE’ does not exist

The reason is that we have not run the related sql statement which supports this function. Below is the scritpt where is coming from.

$ORACLE_HOME/sqlplus/admin/plustrace.sql

We can take a glance at this script to have a understanding of what this function is.

[oracle@TEST11G ~]$ vi $ORACLE_HOME/sqlplus/admin/plustrce.sql
—
— Copyright (c) Oracle Corporation 1995, 2002.  All Rights Reserved.
—
— NAME
—   plustrce.sql
—
— DESCRIPTION
—   Creates a role with access to Dynamic Performance Tables
—   for the SQL*Plus SET AUTOTRACE … STATISTICS command.
—   After this script has been run, each user requiring access to
—   the AUTOTRACE feature should be granted the PLUSTRACE role by
—   the DBA.
—
— USAGE
—   sqlplus “sys/knl_test7 as sysdba” @plustrce
—
—   Catalog.sql must have been run before this file is run.
—   This file must be run while connected to a DBA schema.

set echo on

drop role plustrace;
create role plustrace;

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;

set echo off

So the only thing we need to do is to execute this script by sys system priviledge as following:

[oracle@TEST11G ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 05:48:21 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
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

Up to this step, we all know that this script has been executed successfully. So we can grant plustrace role priviledge to the user who we will need to trace the sql stament execution plan.

SQL> grant plustrace to watson;

Grant succeeded.

In order to show the execution plan successfully, we also need to do the one more steps, which is to create the plan_table by a script offered by oracle,if not executed.

[oracle@TEST11G ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 06:24:28 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

Now the common user watson have the function of trace sql execution plan as following:

SQL> set autotrace traceonly
SQL> select * from t1;

Execution Plan
———————————————————-
Plan hash value: 3617692013

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     2 |   380 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     2 |   380 |     3   (0)| 00:00:01 |
————————————————————————–

Note
—–
   – dynamic sampling used for this statement (level=2)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1442  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

Now the error has been resolved. To conclusion, there are two important scripts we need to know.

1,  $ORACLE_HOME/sqlplus/admin/plustrce.sql

2,  $ORACLE_HOME/rdbms/admin/utlxplan.sql