[Oracle]-[ORA-01031]-create a view based on data dictionary table no permission issues

[Oracle]-[ORA-01031]-create a view based on the data dictionary table without permission issue execution

SQL> create or replace view redo_size as   2  select name, value from v$statname n, v$sesstat t, v$session s   3  where s.audsid = sys_context('USERENV','SESSIONID')  4    and t.sid = s.sid  5    and n.statistic#=t.statistic#  6    and n.name = 'redo size';select name, value from v$statname n, v$sesstat t, v$session s                                                   *ERROR at line 2:ORA-01031: insufficient privileges

 

But executed separately

SQL> select name, value from v$statname n, v$sesstat t, v$session s   2  where s.audsid = sys_context('USERENV','SESSIONID')  3    and t.sid = s.sid  4    and n.statistic#=t.statistic#  5    and n.name = 'redo size';NAME          VALUEredo size      2452

 

Yes. The permissions of the current user include:

ALTER SESSIONCREATE SESSIONUNLIMITED TABLESPACE

Some posts on the Internet said: 1. Grant the select any table permission to this solution object. -An error is reported after the attempt. 2. Use

grant select on v$statname to star

. 3,

grant select any dictionary to test;

-Grant the user the permission to view any dictionary. You can create it after you try it. My understanding: the star user can access dictionary tables such as v $ statname, v $ sesstat, and v $ session separately, but it cannot be used in create view. According to Xi Feifei's article, this may be because of different schemas. In summary, 1) You can create a view if you have the query permission in the same schema. 2) in different schemas, even if you have the query permission to create a view, the ORA-01031 is still prompted.