set autotrace issue

suhinrasheed
suhinrasheed used Ask the Experts™
on
Hi,
I have a schema which has a PLAN_TABLE and also has the PLUSTRACE role and SELECT_CATALOG_ROLE granted to it.
I typed the below sql command

SQL> set autotrace traceonly explain statistics
SQL> select * from tab;

188 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        751  consistent gets
          9  physical reads
          0  redo size
       7493  bytes sent via SQL*Net to client
        767  bytes received via SQL*Net from client
         14  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        188  rows processed
Why is the error coming,whats the fix
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Tab is view based on sys tables. A normal user does not have privileges on these tables. that is why you getting this error. try a select on one of your owned tables.

HTH
Vishal
Naveen KumarProduction Manager / Application Support Manager

Commented:
check whether explain plan command works fine because looks like you are getting error while doing explain plan like

explain plan set statement_id = 'S1'
for select sysdate from dual;

select * from plan_table;

Thanks
I agree with vishal68, you can try the same while connected as sys and it will work fine... you can also try "select * from dual" to verify nothing is broken. In fact, this is expected behaviour!
Forced accept.

Computer101
EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial