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?

1 Answer 1

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

1
  • Thanks Joe, I looked at this post u refered, it was spot on. Sep 26, 2018 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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