PLUSTRACE角色

使用示例数据库用户HR登陆需要打印执行计划等信息的时候，提示了错误，

提示很清楚了，PLUSTRACE角色未赋给HR。

查询HR所有的角色，

确实缺少PLUSTRACE角色，

通过oerr工具，可以进一步查看这个错误，

AUTOTRACE Option in SQL*Plus (文档 ID 43214.1)中说明了要使用AUTOTRACE就必须有PLUSTRACE角色。

You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (Data Manipulation Language - that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.

You can control the report by setting the AUTOTRACE system variable.
SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON - The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the user’s query output, if any.

To use this feature, you must have the PLUSTRACE role granted to you and a PLAN_TABLE table created in your schema. For more information on the PLUSTRACE role and PLAN_TABLE table, see the AUTOTRACE variable of the SET command in Chapter 6 of the SQL*Plus Guide.

解决方法：
1.这个脚本会创建PLUSTRACE角色，

$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

创建了PLUSTRACE角色，将三张v_$的基表访问权限赋予PLUSTRACE角色，然后将PLUSTRACE授权DBA，并且有admin option属性。

2.执行脚本，

3.将PLUSTRACE授予HR，

SQL> grant plustrace to hr;              
Grant succeeded.

4.再次执行SET AUTOTTRACE，

SQL> show user
USER is "HR"
SQL> set autot trace

欢迎关注我的个人微信公众号：bisal的个人杂货铺