SQL beginner needs help! (ORA-00979)

From: Sylvain Tremblay <syltrem_at_videotron.ca>
Date: Mon, 01 Nov 1999 19:24:01 GMT
Message-ID: <01bf249e$ad500b00$68016ea6@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; 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 Received on Mon Nov 01 1999 - 13:24:01 CST