Skip to Main Content
  • Questions

  • how to resolve error or grant access The statistic feature for v_$sesstat, v_$statname and v_$session.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, m.

Asked: February 18, 2016 - 3:22 am UTC

Last updated: February 21, 2016 - 12:05 pm UTC

Version: 4.1.3

Viewed 10K+ times! This question is

You Asked

I am working on SQL developer and installed oracle Xpress also. So I want to use explain plan to know stats.

But it says
Unable to gather statistics please unsure user has correct access.
The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.


SQL> connect hr/password
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SP2-0310: unable to open file "$ORACLE_HOME/rdbms/admin/utlxplan.sql"
SQL>

what I have to do to get prvileges and to use explain plan

and Connor said...

You need to connect as SYSDBA to grant the privileges, eg

SQL> conn sys/xxxx as sysdba
Connected.

SQL> grant select on v_$session to hr;

Grant succeeded.


and so on for the other 2 views. In a non-production environment, you can probably grant select to public if you wanted.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

explain plan

m123, February 18, 2016 - 4:40 am UTC

in sqlplus i mentioned as
</> conn sys/password as sysdba
Connected.
</> grant select on v_$session to hr;
Grant succeeded.

i mentioned in sql developer as
</> explain plan for select * from employees where department_id=40;

set SERVEROUTPUT ON;
set autotrace on;

</> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());</>

Unable to gather statistics please unsure user has correct access.
The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.

what i have to do know please suggest ?


Connor McDonald
February 18, 2016 - 4:42 am UTC

"The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session. "

I've given you the steps to do those grants.

Can you elaborate more on what problem you are having ?

explain plan

m123, February 18, 2016 - 5:25 am UTC

hi Connor,

I granted permission for the three like you mentioned.
</>explain plan for select * from employees where department_id=40;

</>SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

got output as
PLAN_TABLE_OUTPUT
TABLE ACCESS BY INDEX ROWID| EMPLOYEES
INDEX RANGE SCAN

Plan hash value: 2137789089

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

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

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

| 0 | SELECT STATEMENT | | 1 | 69 | 2
(0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 2
(0)| 00:00:01 |


---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------


and below it is mentioned as

Unable to gather statistics please unsure user has correct access.
The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.

what does this mean unable to gather stats mean ?what i have to get correct stastistics ?
Connor McDonald
February 21, 2016 - 12:05 pm UTC

Sorry - I can't reproduce your problem. I just did this on a fresh 4.1.3 install, talking to 12.1.0.2 database

SQL> create user demo identified by demo;

User created.

SQL> grant create session to demo;

Grant succeeded.

SQL>
SQL> grant select on scott.emp to demo;

Grant succeeded.

SQL> grant select on scott.dept to demo;

Grant succeeded.


so "demo" has pretty much the bare minimum. Then did:

select * from scott.emp e, scott.dept d
where e.deptno = d.deptno;

and the explain plan (F10) in SQL Dev...that worked.

Then I did

explain plan for
select * from scott.emp e, scott.dept d
where e.deptno = d.deptno;

select * from table(dbms_xplan.display);

and that works fine too.

Then I clicked Autotrace and got:

Failed to access V$MYSTAT.
Please obtain read catalog privilege
from your database administrator:
grant SELECT_CATALOG_ROLE to DEMO
grant SELECT ANY DICTIONARY to DEMO
NOTE: you need to reconnect your current session
in order for the settings change to have an effect

which is what I would have expected.

So I'm a bit lost on where precisely you're seeing the errors you are seeing.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.