【Oracle】SP2-0613，SP2-0611問題解決

楊奇龍發表於2011-12-06
Oracle
檢視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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章