Re: Create views based on DBA_ views...

In article <3A179FC2.37FF0D3_at_innocent.com>,   Chris Lee <clee_at_innocent.com> wrote:
> Hi,
>
> I am new to database administration. I was trying to create some views
> based on DBA_ views, but I ran into some error. Here is what I did:
>
> * I started sqlplus as SYSTEM.
> * In sqlplus, I tried to create a view:
>
> SQL> CREATE VIEW my_dba_tablespace_sizes AS
> 2 SELECT tablespace_name, sum(bytes) bytes
> 3 FROM dba_data_files
> 4 GROUP BY tablespace_name;
> FROM dba_data_files
> *
> ERROR at line 3:
> ORA-00942: table or view does not exist
>
> Can anyone tell me what the problem was? Any suggestions for fixing
 this
> problem?
>
> Thanks,
>
> -- Chris
>

Views are compiled. During compilation roles are ignored. You have select privilege using a role, which means you don't have privilege when you compile. You could resolve this by making a direct grant, however that would constitute in this specific case, IMO, a *bad* idea, as you will end up with a myriad of uncontrollable direct grants. You are aware you have inline views in Oracle. I have a feeling in which context you try to use them.
You could easily use
select f.tablespace_name, totall, totfre from
(select tablespace_name, sum(bytes() totall  from dba_data_files
 group by tablespace_name) f
,(select tablespace_name, sum(bytes) totfre   from dba_free_space
  group by tablespace_name ) fr
where fr.tablespace_name = f.tablespace_name /

Hth,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.