2

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?

3

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

  • Thanks Joe, I looked at this post u refered, it was spot on. – Flavio Mesquita Sep 26 '18 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.