ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【Oracle】SP2-0613,SP2-0611问题解决

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

原创 Linux操作系统 作者:杨奇龙 时间:2011-12-06 21:43:08 0 删除 编辑
查看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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7531954