set autotrace on 引发的问题

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色
SP2-0611: 启用STATISTICS报告时出现错误

应该是该用户没有权限，以dba身份登录，对sl用户授权

引用

SQL> grant plustrace to sl;
grant plustrace to sl
      *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist 提示没有改角色。

SQL> select role from dba_roles  where role='PLUSTRACE';
no rows selected

数据库系统中没有该角色，但是我们可以通过执行 plustrce.sql($ORACLE_HOME/sqlplus/admin/plustrce.sql)脚本创建 plustrace

引用

--   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 "/ 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

执行完后 数据库中就存在该角色了。

引用

SQL> select role from dba_roles  where role='PLUSTRACE';
ROLE
------------------------------
PLUSTRACE

可以通过查询session_roles表 查看当前用户所拥有的角色

引用

SESSION_ROLES describes the roles that are currently enabled to the user.
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE
PLUSTRACE

但是还是出现下面的问题

引用

ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report

解决方法：

引用

SQl>conn / as sysdba
SQL> grant select any dictionary to test;

引用

advise you against adding this privilege to the users, it's very bad for security! You don't really need the traces for the queries on the SYS-owned objects.