执行计划（SP2-0618\0611问题的解决）

对于初次查看执行计划的用户需要一个plustrace角色，否则就会报下面的错：

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

------#解决这个问题需要跑个脚本（创建plustrace并授权）
---查看脚本内容

1. [oracle@zh ~]$ cd $ORACLE_HOME/sqlplus/admin/
   
2. [oracle@zh admin]$ ll
   
3. total 20
   
4. -rw-r--r-- 1 oracle oinstall 368 Apr 10 2011 glogin.sql
   
5. drwxr-xr-x 2 oracle oinstall 4096 Dec 8 17:34 help
   
6. -rw-r--r-- 1 oracle oinstall 226 Jul 17 2013 libsqlplus.def
   
7. -rw-r--r-- 1 oracle oinstall 813 Mar 7 2006 plustrce.sql
   
8. -rw-r--r-- 1 oracle oinstall 2118 Feb 16 2003 pupbld.sql
   
9. [oracle@zh admin]$ cat plustrce.sql
   
10. --
    
11. -- Copyright (c) Oracle Corporation 1995, 2002. All Rights Reserved.
    
12. --
    
13. -- NAME
    
14. -- plustrce.sql
    
15. --
    
16. -- DESCRIPTION
    
17. -- Creates a role with access to Dynamic Performance Tables
    
18. -- for the SQL*Plus SET AUTOTRACE ... STATISTICS command.
    
19. -- After this script has been run, each user requiring access to
    
20. -- the AUTOTRACE feature should be granted the PLUSTRACE role by
    
21. -- the DBA.
    
22. --
    
23. -- USAGE
    
24. -- sqlplus "sys/knl_test7 as sysdba" @plustrce
    
25. --
    
26. -- Catalog.sql must have been run before this file is run.
    
27. -- This file must be run while connected to a DBA schema.
    
28. 
    
29. set echo on
    
30. 
    
31. drop role plustrace;
    
32. create role plustrace;
    
33. 
    
34. grant select on v_$sesstat to plustrace;
    
35. grant select on v_$statname to plustrace;
    
36. grant select on v_$mystat to plustrace;
    
37. grant plustrace to dba with admin option;
    
38. 
    
39. set echo off
    
40. [oracle@zh admin]$

---跑脚本

1. SQL> drop role plustrace;
   
2. drop role plustrace
   
3.           *
   
4. ERROR at line 1:
   
5. ORA-01919: role 'PLUSTRACE' does not exist
   
6. 
   
7. 
   
8. SQL> create role plustrace;
   
9. 
   
10. Role created.
    
11. 
    
12. SQL>
    
13. SQL> grant select on v_$sesstat to plustrace;
    
14. 
    
15. Grant succeeded.
    
16. 
    
17. SQL> grant select on v_$statname to plustrace;
    
18. 
    
19. Grant succeeded.
    
20. 
    
21. SQL> grant select on v_$mystat to plustrace;
    
22. 
    
23. Grant succeeded.
    
24. 
    
25. SQL> grant plustrace to dba with admin option;
    
26. 
    
27. Grant succeeded.
    
28. 
    
29. SQL>
    
30. SQL> set echo off
    
31. SQL>

---还需要授权

1. SQL> grant PLUSTRACE to hr;
   
2. 
   
3. Grant succeeded.
   
4. 
   
5. SQL>
   
6. SQL>
   
7. SQL> conn hr/hr
   
8. Connected.
   
9. SQL> set autotrace on
   
10. SQL>
    
11. SQL> set autotrace off

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31399171/viewspace-2131415/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31399171/viewspace-2131415/