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

Sylvain Tremblay <syltrem_at_videotron.ca> wrote in message news:01bf249e$ad500b00$68016ea6_at_iva104...
> 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;

Try this:

    group by free.tablespace_name, tot.file_name, tot.bytes

> 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;

Be ware that:
1. The space allocated by an object may span tablespaces. 2. The objects of different types may have the same segment_name.

So, it's better to use:

select owner, segment_name, segment_type,

       sum(extents), sum(bytes/1024) "Used Space" from dba_segments
group by owner, segment_name, segment_type having sum(extents)>10; Received on Tue Nov 02 1999 - 12:32:33 CST