ORA-01039: insufficient privileges onunderlying objects of the View

The view's basic object has insufficient permissions and has been plagued for several days. This problem is solved by Ye Fei, I changed the dept table to view the execution plan. The process is as follows:

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 cballs

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

 

 

I changed an access object:

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 cballs

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

 

So how can we solve the problem of viewing the tab execution plan?

 

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 cballs

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