解锁scott用户后查看执行计划，报如下错误：
http://www.cndba.cn/dave/article/1995

http://www.cndba.cn/dave/article/1995 http://www.cndba.cn/dave/article/1995http://www.cndba.cn/dave/article/1995

[oracle@dave.cndba.cn ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 12 18:02:31 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION

1 row selected.

没有足够的权限，使用sys用户给scott用户赋权：

SQL> conn sys/oracle@dave as sysdba
Connected.
SQL> grant select on v_$sql to scott;
Grant succeeded.

SQL> grant select on v_$session to scott;
Grant succeeded.

SQL> grant select on v_$sql_plan_statistics_all to scott;
Grant succeeded.

再次查询正常，随笔计之。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  70xt266nm9y32, child number 0
-------------------------------------
select e.ename,e.job,d.dname from emp e,dept d  where e.deptno=d.deptno
and e.sal<2000

Plan hash value: 844388907

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      8 |00:00:00.01 |      11 |       |       |          |
|   1 |  MERGE JOIN                  |         |      1 |      4 |      8 |00:00:00.01 |      11 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       4 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
|*  4 |   SORT JOIN                  |         |      4 |      4 |      8 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL         | EMP     |      1 |      4 |      8 |00:00:00.01 |       7 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")
   5 - filter("E"."SAL"<2000)

http://www.cndba.cn/dave/article/1995

版权声明：本文为博主原创文章，未经博主允许不得转载。