一、问题说明
业务用户想使用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!