在普通用户下查看执行计划 时 ,会遇到如下问题:
SQL> CONN SCOTT/SCOTT
已连接。
SQL> SET AUTOT TRACE
搜集统计信息时出错 ORA-942
SP2-0611: 启用 STATISTICS 报告时出错
SQL> SET AUTOT ON
搜集统计信息时出错 ORA-942
SP2-0611: 启用 STATISTICS 报告时出错
SQL> SET AUTOT OFF
SQL> SET AUTOT ON
搜集统计信息时出错 ORA-942
SP2-0611: 启用 STATISTICS 报告时出错
一般导致AUTOTRACE失败不是因为PLAN_TABLE不存在,就是由于缺少PLUSTRACE角色或PLUSTRACE角色包含的视图权限。
SQL> grant plustrace to scott;
grant plustrace to scott
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist 可见plustrace 角色不存在。
SQL> create role plustrace ;
Role created.
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v$mystat to plustrace;
grant select on v$mystat to plustrace
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views --容易犯的错误。
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to scott;
Grant succeeded.
SQL> conn scott/scott
Connected.
SQL> set autot on
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 1554282393
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
5 physical reads
0 redo size
776 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-673050/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22664653/viewspace-673050/