autotrace setups

 SQL> show user;
USER 为 "SYS"
SQL> @D:/oracle/product/10.2.0/db_1/RDBMS/ADMIN/utlxplan.sql;

表已创建。

SQL> @D:/oracle/product/10.2.0/db_1/sqlplus/admin/plustrce.sql;
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
第 1 行出现错误:
ORA-01919: 角色 'PLUSTRACE' 不存在

SQL> create role plustrace;

角色已创建。

SQL>
SQL> grant select on v_$sesstat to plustrace;

授权成功。

SQL> grant select on v_$statname to plustrace;

授权成功。

SQL> grant select on v_$mystat to plustrace;

授权成功。

SQL> grant plustrace to dba with admin option;

授权成功。

SQL>
SQL> set echo off
SQL> GRANT PLUSTRACE TO scott;

授权成功。

SQL> conn scott/tiger
已连接。
SQL> set autotrace on;
SQL> select object_name from user_objects;

OBJECT_NAME                                                                    
--------------------------------------------------------------------------------
PK_DEPT                                                                        
DEPT                                                                           
EMP                                                                            
PK_EMP                                                                         
BONUS                                                                          
SALGRADE                                                                       

已选择6行。

执行计划
----------------------------------------------------------                     
ERROR:
ORA-01039: 视图基本对象的权限不足

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
        623  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        507  bytes sent via SQL*Net to client                                  
        400  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          6  rows processed                                                    

SQL> conn / as sysdba
已连接。
SQL> grant select on PLAN_TABLE to scott;

授权成功。

SQL> conn scott/tiger
已连接。
SQL> set autotrace on;
SQL> select object_name from user_objects;

OBJECT_NAME                                                                    
--------------------------------------------------------------------------------
PK_DEPT                                                                        
DEPT                                                                           
EMP                                                                            
PK_EMP                                                                         
BONUS                                                                          
SALGRADE                                                                       

已选择6行。

执行计划
----------------------------------------------------------                     
ERROR:
ORA-01039: 视图基本对象的权限不足

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
        623  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        507  bytes sent via SQL*Net to client                                  
        400  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          6  rows processed                                                    

SQL> conn / as sysdba
已连接。
SQL> grant plustrace to public;

授权成功。

SQL> grant select on plan_table to public;

授权成功。

SQL> conn scott/tiger
已连接。
SQL> set autotrace on;

SQL> select object_name from user_objects;

OBJECT_NAME                                                                    
--------------------------------------------------------------------------------
PK_DEPT                                                                        
DEPT                                                                           
EMP                                                                            
PK_EMP                                                                         
BONUS                                                                          
SALGRADE                                                                       

已选择6行。

执行计划
----------------------------------------------------------                     
ERROR:
ORA-01039: 视图基本对象的权限不足

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
        623  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        507  bytes sent via SQL*Net to client                                  
        400  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          6  rows processed                                                    

SQL> conn / as sysdba
已连接。
SQL> grant select any dictionary to scott;

授权成功。

SQL> conn scott/tiger
已连接。
SQL> set autotrace on;
SQL> select object_name from user_objects;

OBJECT_NAME                                                                    
--------------------------------------------------------------------------------
PK_DEPT                                                                        
DEPT                                                                           
EMP                                                                            
PK_EMP                                                                         
BONUS                                                                          
SALGRADE                                                                       

已选择6行。

执行计划
----------------------------------------------------------                     
Plan hash value: 269867835                                                     
                                                                               
--------------------------------------------------------------------------------
---------------                                                                
                                                                               
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%C
PU)| Time     |                                                                
                                                                               
--------------------------------------------------------------------------------
---------------                                                                
                                                                               
|   0 | SELECT STATEMENT               |              |  1236 | 81576 |    26  
(4)| 00:00:01 |                                                                
                                                                               
|   1 |  VIEW                          | USER_OBJECTS |  1236 | 81576 |    26  
(4)| 00:00:01 |                                                                
                                                                               
|   2 |   UNION-ALL                    |              |       |       |        
   |          |                                                                
                                                                               
|*  3 |    FILTER                      |              |       |       |        
   |          |                                                                
                                                                               
|*  4 |     TABLE ACCESS FULL          | OBJ$         |  1388 |    98K|    26  
(4)| 00:00:01 |                                                                
                                                                               
|*  5 |     TABLE ACCESS BY INDEX ROWID| IND$         |     1 |     7 |     2  
(0)| 00:00:01 |                                                                
                                                                               
|*  6 |      INDEX UNIQUE SCAN         | I_IND1       |     1 |       |     1  
(0)| 00:00:01 |                                                                
                                                                               
|*  7 |    INDEX RANGE SCAN            | I_LINK1      |     1 |    79 |     0  
(0)| 00:00:01 |                                                                
                                                                               
--------------------------------------------------------------------------------
---------------                                                                
                                                                               
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   3 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1
 FROM                                                                          
                                                                               
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."T
YPE#"=2 OR                                                                     
                                                                               
              "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 O
R "I"."TYPE#"=9))=1)                                                           
                                                                               
   4 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"<>'_NEXT_OBJECT' AN
D                                                                              
                                                                               
              "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NUL
L AND                                                                          
                                                                               
              BITAND("O"."FLAGS",128)=0)                                       
   5 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 O
R                                                                              
                                                                               
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)                 
   6 - access("I"."OBJ#"=:B1)                                                  
   7 - access("L"."OWNER#"=USERENV('SCHEMAID'))                                

统计信息
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
        623  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        507  bytes sent via SQL*Net to client                                  
        400  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          6  rows processed