【Oracle】SP2-0613，SP2-0611问题解决

查看sql的执行计划遇到如下报错：

sys@im1>explain plan for SELECT JOB, LOG_USER, SCHEMA_USER FROM DBA_JOBS;

explain plan for SELECT JOB, LOG_USER, SCHEMA_USER FROM DBA_JOBS

                                                      *

ERROR at line 1:

ORA-02402: PLAN_TABLE not found

sys@im1>set autot trace on;

SP2-0613: Unable to verify PLAN_TABLE format or existence

SP2-0611: Error enabling EXPLAIN report

sys@im1>exit

该错误的的主要原因是由于当前用户下没有PLAN_TABLE这张表及相应的PLUSTRACE角色权限。

解决方法就是直接创建此表和对应的PLUSTRACE角色即可

1 以sys 用户登录数据库，运行utlxplan.sql脚本创建 PLAN_TABLE并赋予相关权限，以便让其他用户使用

oracle@t01a03053-vm1.corp-hz01.aliyun.com:/home/oracle/admin/bin>sqlplus "/as sysdba"

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

sys@im1>@?/rdbms/admin/utlxplan.sql 

Table created.

sys@im1>create public synonym plan_table for plan_table; 

grant all on plan_table to public;

Synonym created.

sys@im1>

Grant succeeded.

2 通过执行 plustrce.sql脚本创建 plustrace 角色,这将V$视图上的选择权限授予该角色,也将plustrace角色授予DBA角色：

sys@im1>@/opt/oracle/products/9.2.0/sqlplus/admin/plustrce.sql

sys@im1>

sys@im1>drop role plustrace;

drop role plustrace

          *

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

sys@im1>create role plustrace;

Role created.

sys@im1>

sys@im1>grant select on v_$sesstat to plustrace;

Grant succeeded.

sys@im1>grant select on v_$statname to plustrace;

Grant succeeded.

sys@im1>grant select on v_$mystat to plustrace;

Grant succeeded.

sys@im1>grant plustrace to dba with admin option;

Grant succeeded.

sys@im1>

sys@im1>set echo off

sys@im1>

sys@im1>

再次执行：

sys@im1>explain plan for SELECT JOB, LOG_USER, SCHEMA_USER FROM DBA_JOBS;

Explained.

sys@im1>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |       |       |       |

|   1 |  TABLE ACCESS FULL   | JOB$        |       |       |       |

--------------------------------------------------------------------

Note: rule based optimization

9 rows selected.

ok！

    

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

转载于:http://blog.itpub.net/22664653/viewspace-712836/