[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.