v$Instance permission errors

Question

Hi all,

I wanted to create a view that looked something like the following but i keep getting an ORA-01031 - insufficient permission error

create view v_dbinfo as
Select INSTANCE_NAME,HOST_NAME from v$instance;

I can select from v$instance, and create a view from an existing table without any problems. Any idea on why this is occurring and how i can go about fixing it?

Thanks

Answer 1

I would tend to wager that you have access to V$INSTANCE via a role rather than as a direct grant. If you want to create a view (or reference V$INSTANCE in a definer's rights stored procedure), you would need to have been granted access to the referenced objects via direct grants, not via a role.

In addition, if you intend on granting access to this new view to other users, you will need the access to V$INSTANCE to be granted using the WITH GRANT OPTION clause, i.e.

GRANT SELECT ON v$instance
   TO your_user_name
 WITH GRANT OPTION;