无法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:如果您想和业内技术大牛交流的话,请加qq群(527933790)或者关注微信公众 号(AskHarries),谢谢!

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

赞 (0)

分享到:更多 ()

评论 0

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