DB version: 11.2.0.4
OS version: RHEL 6.5
I've performed the following steps to enable AUTOTRACE for an Application user in SQL*Plus. But, I keep getting the following error
ORA-01039: insufficient privileges on underlying objects of the view
Following are the 3 steps I've performed
Step1.
--- Starting plustrce.sql execution as SYS
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
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
---End of plustrace.sql script execution
Step2.
---granting PLUSTRACE role to application user
SQL> grant plustrace to siebel;
Step3.
--- Logging in as application user and creating Plan Table
$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 3 17:48:04 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: siebel
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show user
USER is "SIEBEL"
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.
--- Now, trying to run AUTOTRACE for a SELECT statement. But, I keep getting ORA-01039
SQL> SET AUTOTRACE ON
SQL> select count(*) from user_objects;
COUNT(*)
----------
20193
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
13783 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
I tried the following grant too. But it didn't fix ORA-01039 error
SQL> grant plustrace to public;