Re: SQL beginner needs help! (ORA-00979)

From: Mike Mountjoy <michael_at_london.virgin.net>
Date: Tue, 02 Nov 1999 09:01:17 +0000
Message-ID: <381EA85D.DB96105F@london.virgin.net>

select substr(free.tablespace_name,1,15), substr(tot.file_name,1,40), tot.bytes/1024 "Total Space", sum(free.bytes/1024) "FreeSpace", (sum(free.bytes) / tot.bytes)
from dba_FREE_SPACE free, dba_data_files tot where tot.file_id = free.file_id
group by free.tablespace_name, tot.file_name;

The elements of the group by must be in the select statement.
All elements in the select expression which are not group functions (avg, sum , count etc) must be included in the group by e.g

select count(*) , car_type
from car
group by car_types;

would give :        ford 20
                          renault 40
                          nissan 100

here count is a group expression , car_types is not

3. You can't use an alias in the group by part of the statement since the alias name isn't returned

until the result set is calculated.

tedchyn_at_yahoo.com wrote:

> In article <01bf249e$ad500b00$68016ea6_at_iva104>,
> "Sylvain Tremblay" <syltrem_at_videotron.ca> wrote:
> > Hello all!
> >
> > I'm new with Oracle./ SQL, and I'm trying to find out how much % space
> is
> > used in my database. Is below a good way of doing it ? If I'm heading
> the
> > wrong direction, can anyone suggest something better ? In any case,
> can you
> > explain the error message I'm getting, for I don't know what's causing
> it.
> >
> > SQL> select substr(free.tablespace_name,1,15),
> substr(tot.file_name,1,40),
> > 2 tot.bytes/1024 "Total Space", sum(free.bytes/1024) "Free
> > Space",
> > 3 (sum(free.bytes) / tot.bytes)
> > 4 from dba_FREE_SPACE free, dba_data_files tot
> > 5 where tot.file_id = free.file_id
> > 6 group by free.tablespace_name, tot.file_name;
> > select substr(free.tablespace_name,1,15), substr(tot.file_name,1,40),
> > *
> > ERROR at line 1:
> > ORA-00979: not a GROUP BY expression
> >
> > This other one tells how many extents are used up by tables,
> indexes... Is
> > ther a weay to get only those for which more than 10 extents are used
> ?
> >
> > select substr(tablespace_name,1,15), substr(owner,1,12),
> > substr(segment_name,1,15), sum(extents), sum(bytes/1024) "Used
> > Space"
> > from dba_segments
> > group by tablespace_name, owner, segment_name;
> >
> > Many many thanks!
> >
> > Sylvain Tremblay
> > http://pages.infinit.net/syltrem

> >
> sylvain, try this (tells if table's next extent is exceeding the free
> space available for next extension).

>


> select tablesapce_name, next_extent


>       from user_tables x


>       where not exists (


>              select *


>              from dba_free_space y


>              where x.tablespace_name = y.tablespace_name


>                    and y.bytes >= x.next_extent);


>

> >

> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Nov 02 1999 - 03:01:17 CST