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

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 Mon Nov 01 1999 - 18:05:59 CST