Re: query help

Doug Cowles <dcowles_at_bigfoot.com> wrote in message news:37B9055E.612D218B_at_bigfoot.com...
> I'm trying to get a list of tablespaces where the largest chunk of free
> space is less than
> the largest next_extent of objects in the tablespace -
> I'm trying
> SQL> select a.tablespace_name, max(bytes) from dba_free_space a
> 2 where max(bytes) < (select max(next_extent) from dba_segments b
> 3 where a.tablespace_name = b.tablespace_name);
> where max(bytes) < (select max(next_extent) from dba_segments b
> *
> ERROR at line 2:
> ORA-00934: group function is not allowed here
>
> What am I doing wrong?
>

You can not use group functions in where clause. Use them in HAVING clause.
The correct verion is:

select a.tablespace_name, max(a.bytes)
  from dba_free_space a, dba_segments b   where a.tablespace_name=b.tablespace_name   group by a.tablespace_name
  having max(a.bytes)<max(next_extent);

You can use a more better version:

select a.tablespace_name, a.bytes
  from
  (select tablespace_name, max(bytes) as bytes

     from dba_free_space group by tablespace_name) a,   (select tablespace_name, max(next_extent) as next_extent

     from dba_segments group by tablespace_name) b   where a.tablespace_name=b.tablespace_name     and bytes<next_extent;

However, the fastest way is to access underlaying tables directly, it's about 10 times faster then the prior one:

select t.name, max(f.length*t.blocksize) as bytes   from sys.seg$ s, sys.fet$ f, sys.ts$ t   where t.ts#=s.ts#
    and t.ts#=f.ts#
  group by t.ts#, t.name
  having max(f.length)<max(s.extsize); Received on Wed Aug 18 1999 - 06:38:31 CDT