Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01031 (10g)
ORA-01031 [message #353371] Mon, 13 October 2008 11:31 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
I'm trying to create a view based on Tom's query.

But when I try this


create or replace view sys_free_space_vw as
select (select decode(extent_management,'LOCAL','*',' ') ||
               decode(segment_space_management,'AUTO','a ','m ')
          from dba_tablespaces where tablespace_name = b.tablespace_name) ||
        nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       ((kbytes_alloc-nvl(kbytes_free,0))/
                          kbytes_alloc)*100 pct_used,
       nvl(largest,0) largest,
       nvl(kbytes_max,kbytes_alloc) Max_Size,
       decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              sum(maxbytes)/1024 Kbytes_max,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name
       union all
      select sum(bytes)/1024 Kbytes_alloc,
              sum(maxbytes)/1024 Kbytes_max,
              tablespace_name
       from sys.dba_temp_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name



I get ORA-01031. But with simply executing the query (not creating the view) is ok. So does this mean I also need direct access to the dba* view/table to create a view out of it? Thanks.
Re: ORA-01031 [message #353372 is a reply to message #353371] Mon, 13 October 2008 11:41 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I think you are not having a create view privilege. However could you please confirm from where you are executing this piece of sql ?

Regards

Raj
Re: ORA-01031 [message #353373 is a reply to message #353372] Mon, 13 October 2008 11:48 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
I can execute it as is in TOAD, without the create view part. It's just on when I'm trying to create a view based on the query that I'm getting that error. Do the DBA's need to grant direct grants to dba views or is it a security issue?

*I just need some direct grants =( But not sure if that's a "security issue".

[Updated on: Mon, 13 October 2008 12:03]

Report message to a moderator

Re: ORA-01031 [message #353376 is a reply to message #353371] Mon, 13 October 2008 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure you carefully read the prerequisites of CREATE VIEW?

Regards
Michel
Re: ORA-01031 [message #353381 is a reply to message #353376] Mon, 13 October 2008 12:44 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
You mean the direct grant needed?
Re: ORA-01031 [message #353382 is a reply to message #353381] Mon, 13 October 2008 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: ORA-01031 [message #353383 is a reply to message #353382] Mon, 13 October 2008 12:48 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks again. I updated the message above on that. Also why was it restricted selecting on those? I don't see any *harm* if someone sees the free space?
Re: ORA-01031 [message #353388 is a reply to message #353383] Mon, 13 October 2008 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Also why was it restricted selecting on those? I don't see any *harm* if someone sees the free space?

Do you mean why dba_free_space is not public? Why user should have to see this information? The basic principle of security is least privilege: no one should have any privilege that is not necessary to do his/her job.

Regards
Michel
Re: ORA-01031 [message #353391 is a reply to message #353388] Mon, 13 October 2008 13:18 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Oh ok, Thank you very much for that.
Previous Topic: New in PL/SQL Triggers
Next Topic: update query
Goto Forum:
  


Current Time: Wed Dec 07 02:49:21 CST 2016

Total time taken to generate the page: 0.11545 seconds