转载

无法set autotrace on 报错SP2-0618，SP2-0611

一、问题说明

业务用户想使用set autotrace on直接来查看SQL的执行计划时，报错SP2-0618，SP2-0611,问题是缺少对应权限。

二、问题重现与处理

1.问题重现

DZWJ_USER@orcl1 > set autot on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

2.给用户赋予PLUSTRACE这个角色的权限，但是没有此角色

SYS@orcl1 > grant plustrace to dzwj_user;
grant plustrace to dzwj_user
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

3.查看官方文档后得知需要执行plustrce.sql脚本来建立plustrace角色

[oracle@zw-oradb01 ~]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@zw-oradb01 admin]$ ls
glogin.sql help libsqlplus.def plustrce.sql pupbld.sql
[oracle@zw-oradb01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 8 15:17:58 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@orcl1 > @plustrce.sql
SYS@orcl1 >
SYS@orcl1 > drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SYS@orcl1 > create role plustrace;
Role created.
SYS@orcl1 >
SYS@orcl1 > grant select on v_$sesstat to plustrace;
Grant succeeded.
SYS@orcl1 > grant select on v_$statname to plustrace;
Grant succeeded.
SYS@orcl1 > grant select on v_$mystat to plustrace;
Grant succeeded.
SYS@orcl1 > grant plustrace to dba with admin option;
Grant succeeded.
SYS@orcl1 >
SYS@orcl1 > set echo off
SYS@orcl1 >

4.赋权PLUSTRACE角色给用户

SYS@orcl1 > grant PLUSTRACE to dzwj_user;
Grant succeeded.

5.再次使用autotrace正常，注意：如果仍然报同样的错，可以手动赋权。语句如下：

SYS@orcl1 > grant select on v_$sesstat to dzwj_user;
Grant succeeded.
SYS@orcl1 > grant select on v_$statname to dzwj_user;
Grant succeeded.
SYS@orcl1 > grant select on v_$mystat to dzwj_user;
Grant succeeded.

6.再次验证

SYS@orcl1 > conn dzwj_user
Enter password:
Connected.
DZWJ_USER@orcl1 > set autotrace on
DZWJ_USER@orcl1 > select count(*) from dual;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3910148636
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
526 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)
1 rows processed

三、总结

DBA经常会要看SQL语句的执行计划来判断是否需要优化，所以就要在业务用户下进行set autotrace on的简单操作，可ORACLE对于非管理员用户的权限上管理得很严格，这次我就遇到了。还好现在是万能的网络时代，问题一下子就找到了。自己实验一把，算是加深一下印象，又是好久没有发文了，再一次被自己的lazy打败了。给自己加加油，Fighting! Fighting! Fighting!

正文到此结束