ORA-01039: insufficient privileges onunderlying objects of the view

视图基本对象的权限不足，一直困扰好几天的问题，让业飞一看就解决了，⊙﹏⊙b汗啊，对tab视图的访问权限不够啊，果然换了一张表dept就可以查看执行计划了。过程如下：

SQL> conn scott/tiger

Connected.

SQL> set autotrace on;

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

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

DEPT                           TABLE

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

MYTABLE                        TABLE

 

 

Execution Plan

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

ERROR:

ORA-01039: insufficient privileges onunderlying objects of the view

SP2-0612: Error generating AUTOTRACEEXPLAIN report

 

Statistics

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

         0  recursive calls

         0  db block gets

       629  consistent gets

         0  physical reads

         0  redo size

       611  bytes sent via SQL*Net toclient

       385  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         5  rows processed

 

 

换了一张访问对象：

SQL> select * from dept;

 

   DEPTNO DNAME          LOC

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

       10 ACCOUNTING     NEW YORK

       20 RESEARCH       DALLAS

       30 SALES          CHICAGO

       40 OPERATIONS     BOSTON

 

 

Execution Plan

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

Plan hash value: 3383998547

 

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

| Id | Operation         | Name |Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0| SELECT STATEMENT  |      |    4 |   120 |     3  (0)| 00:00:01 |

|   1|  TABLE ACCESS FULL| DEPT |     4 |  120 |     3   (0)| 00:00:01 |

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

 

Note

-----

   -dynamic sampling used for this statement

 

 

Statistics

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

       340  recursive calls

         0  db block gets

        72  consistent gets

         8  physical reads

         0  redo size

       647  bytes sent via SQL*Net toclient

       385  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         6  sorts (memory)

         0  sorts (disk)

         4  rows processed

 

那么，如何解决查看tab的执行计划呢？

 

SQL> show user

USER is "SYS"

SQL> grant select any dictionary toscott;

 

Grant succeeded.

SQL> conn scott/tiger

Connected.

SQL> set autot traceonly      

SQL> select * from tab;

 

 

Execution Plan

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

Plan hash value: 457676135

 

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

| Id | Operation             |Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0| SELECT STATEMENT      |        |  998 | 78842 |   113   (1)| 00:00:02 |

|   1|  NESTED LOOPS OUTER   |       |   998 | 78842 |   113  (1)| 00:00:02 |

|*  2|   TABLE ACCESS FULL   | OBJ$  |   998 | 72854 |    26  (4)| 00:00:01 |

|   3|   TABLE ACCESS CLUSTER| TAB$   |    1 |     6 |     1  (0)| 00:00:01 |

|*  4|    INDEX UNIQUE SCAN  | I_OBJ# |    1 |       |     0  (0)| 00:00:01 |

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

 

Predicate Information (identified by operationid):

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

 

   2- filter("O"."TYPE#"<=5 AND"O"."OWNER#"=USERENV('SCHEMAID') AND

             "O"."TYPE#">=2 AND"O"."LINKNAME" IS NULL)

   4- access("O"."OBJ#"="T"."OBJ#"(+))

 

 

Statistics

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

         0  recursive calls

         0  db block gets

       629  consistent gets

         0  physical reads

         0  redo size

       611  bytes sent via SQL*Net toclient

       385  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         5  rows processed