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

Hi Sylvain,

As to the error message:
a GROUP BY expression MUST occur in the select list. Your substr(free.tablespace_name, 1,15) doesn't match your group by clause. Same with respect to tot.file_name.
Your second statement is incorrect. The dba_segments view already is a summary of dba_extents. You don't need the group by and sum, you should replace the group by with an order by.
I'm not too sure about your first statement. AFAIK, free space isn't tracked on file level.

Hth,

--
Sybrand Bakker, Oracle DBA
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;
> 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 - 16:31:31 CST