cannot create view in oracle 12c

Question

I have a simple query like this:

select d.file_name, d.tablespace_name, s.segment_type, s.segment_name,
round(s.bytes/1024/1024,2) MB
from dba_data_files d
inner join user_segments s on d.tablespace_name = s.tablespace_name;

It works fine. But what i really want is to create a view with this query. So i put:

create view espaco as
select d.file_name, d.tablespace_name, s.segment_type, s.segment_name,
round(s.bytes/1024/1024,2) MB
from dba_data_files d
inner join user_segments s on d.tablespace_name = s.tablespace_name;

But it doesnt work, it says:

ERROR at line 4:

ORA-01031: insufficient privileges

So, how come I can do this query normally, but if I create view it doesnt work? I can create other views, but when I create a view to search from dba_data_files it doesn`t work. The user has create view privileges, dba privileges, any privileges, and still doesnt work. Does anyone have any idea why?

Answer 1

It sounds like you don't have direct access to the dba views and have it granted through a role. You can check out this answer on Stack Overflow for a little more information.

https://stackoverflow.com/questions/28936449/grant-create-view-using-system-view-with-dba-privilage-in-oracle