Re: query help

From: Michel Cadot <micadot_at_altern.org>
Date: Tue, 17 Aug 1999 10:53:38 +0200
Message-ID: <7pb7vk$d1h$1@oceanite.cybercable.fr>

Actually, you cannot use the group function max directly in the where clause, you have to use the having clause to select the groups (here the tablespace) you want: select a.tablespace_name, max(bytes) from dba_free_space a   having max(bytes) < (select max(next_extent) from dba_segments b

                      where a.tablespace_name = b.tablespace_name)

group by a.tablespace_name;

I prefer the following query which is 4 times speedier and gives you the requested next extent:
select a.tablespace_name,

       max(b.bytes) "Max Free",
       max(c.next_extent) "Max Next"

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

Doug Cowles a écrit dans le message <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?
>
>- Dc.
>
Received on Tue Aug 17 1999 - 03:53:38 CDT