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

一、问题说明
       业务用户想使用set autotrace on直接来查看SQL的执行计划时,报错SP2-0618,SP2-0611,问题是缺少对应权限。
二、问题重现与处理
1.问题重现

  1. DZWJ_USER@orcl1 > set autot on
  2. SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
  3. SP2-0611: Error enabling STATISTICS report
2.给用户赋予PLUSTRACE这个角色的权限,但是没有此角色

  1. SYS@orcl1 > grant plustrace to dzwj_user;
  2. grant plustrace to dzwj_user
  3.       *
  4. ERROR at line 1:
  5. ORA-01919: role ‘PLUSTRACE’ does not exist
3.查看官方文档后得知需要执行plustrce.sql脚本来建立plustrace角色


  1. [oracle@zw-oradb01 ~]$ cd $ORACLE_HOME/sqlplus/admin
  2. [oracle@zw-oradb01 admin]$ ls
  3. glogin.sql help libsqlplus.def plustrce.sql pupbld.sql
  4. [oracle@zw-oradb01 admin]$ sqlplus / as sysdba
  5. SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 8 15:17:58 2017
  6. Copyright (c) 1982, 2013, Oracle. All rights reserved.
  7. Connected to:
  8. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  9. With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
  10. Data Mining and Real Application Testing options
  11. SYS@orcl1 > @plustrce.sql
  12. SYS@orcl1 >
  13. SYS@orcl1 > drop role plustrace;
  14. drop role plustrace
  15.           *
  16. ERROR at line 1:
  17. ORA-01919: role ‘PLUSTRACE’ does not exist
  18. SYS@orcl1 > create role plustrace;
  19. Role created.
  20. SYS@orcl1 >
  21. SYS@orcl1 > grant select on v_$sesstat to plustrace;
  22. Grant succeeded.
  23. SYS@orcl1 > grant select on v_$statname to plustrace;
  24. Grant succeeded.
  25. SYS@orcl1 > grant select on v_$mystat to plustrace;
  26. Grant succeeded.
  27. SYS@orcl1 > grant plustrace to dba with admin option;
  28. Grant succeeded.
  29. SYS@orcl1 >
  30. SYS@orcl1 > set echo off
  31. SYS@orcl1 >
4.赋权PLUSTRACE角色给用户

  1. SYS@orcl1 > grant PLUSTRACE to dzwj_user;
  2. Grant succeeded.
5.再次使用autotrace正常,注意:如果仍然报同样的错,可以手动赋权。语句如下:

  1. SYS@orcl1 > grant select on v_$sesstat to dzwj_user;
  2. Grant succeeded.
  3. SYS@orcl1 > grant select on v_$statname to dzwj_user;
  4. Grant succeeded.
  5. SYS@orcl1 > grant select on v_$mystat to dzwj_user;
  6. Grant succeeded.
6.再次验证

  1. SYS@orcl1 > conn dzwj_user
  2. Enter password:
  3. Connected.
  4. DZWJ_USER@orcl1 > set autotrace on
  5. DZWJ_USER@orcl1 > select count(*) from dual;
  6.   COUNT(*)
  7. ———-
  8.          1
  9. Execution Plan
  10. ———————————————————-
  11. Plan hash value: 3910148636
  12. —————————————————————–
  13. | Id | Operation | Name | Rows | Cost (%CPU)| Time |
  14. —————————————————————–
  15. | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
  16. | 1 | SORT AGGREGATE | | 1 | | |
  17. | 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
  18. —————————————————————–
  19. Statistics
  20. ———————————————————-
  21.           1 recursive calls
  22.           0 db block gets
  23.           0 consistent gets
  24.           0 physical reads
  25.           0 redo size
  26.         526 bytes sent via SQL*Net to client
  27.         524 bytes received via SQL*Net from client
  28.           2 SQL*Net roundtrips to/from client
  29.           0 sorts (memory)
  30.           0 sorts (disk)
  31.           1 rows processed
三、总结
       DBA经常会要看SQL语句的执行计划来判断是否需要优化,所以就要在业务用户下进行set autotrace on的简单操作,可ORACLE对于非管理员用户的权限上管理得很严格,这次我就遇到了。还好现在是万能的网络时代,问题一下子就找到了。自己实验一把,算是加深一下印象,又是好久没有发文了,再一次被自己的lazy打败了。给自己加加油,Fighting! Fighting! Fighting! 




本站部分文章源于互联网,本着传播知识、有益学习和研究的目的进行的转载,为网友免费提供。如有著作权人或出版方提出异议,本站将立即删除。如果您对文章转载有任何疑问请告之我们,以便我们及时纠正。

PS:推荐一个微信公众号: askHarries 或者qq群:474807195,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化这些成为架构师必备的知识体系。还能领取免费的学习资源,目前受益良多

转载请注明原文出处:Harries Blog™ » 无法set autotrace on 报错SP2-0618,SP2-0611

赞 (0)
分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址