Authorize scott to enable the execution plan. The script is as follows:
[Oracle @ rac1 admin] $ sqlplus/as sysdba
SQL * Plus: Release 10.2.0.5.0-Production on Sat Oct 12 02:02:28 2013
Copyright (c) 1982,201 0, Oracle. All Rights Reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
And Real Application Testing options
SQL> @ $ ORACLE_HOME/rdbms/admin/utlxplan. SQL
Table created.
SQL> @ $ ORACLE_HOME/sqlplus/admin/plustrce. SQL
SQL>
SQL> drop role plustrace;
Role dropped.
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v _ $ sesstat to plustrace;
Grant succeeded.
SQL> grant select on v _ $ statname to plustrace;
Grant succeeded.
SQL> grant select on v _ $ mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off
SQL> grant plustrace to scott;
Grant succeeded.
Use scott to enable the execution plan
SQL> conn scott
Enter password:
Connected.
SQL> set autotrace on;
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
-----------------------------------------------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
T TABLE
E1 TABLE
6 rows selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating autotrace explain report
Statistics
----------------------------------------------------------
287 recursive cballs
0 db block gets
709 consistent gets
1 physical reads
0 redo size
755 bytes sent via SQL * Net to client
492 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
6 rows processed
ORA-00600 [2662] troubleshooting
Troubleshooting for ORA-01078 and LRM-00109
Notes on ORA-00471 Processing Methods
ORA-00314, redolog corruption, or missing Handling Methods
Solution to ORA-00257 archive logs being too large to store