查看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！
