Re: query help

From: Alex Hudghton <alex_at_alenda.freeserve.co.uk>
Date: Tue, 17 Aug 1999 08:56:31 +0100
Message-ID: <7pb4jk$gkl$1@taliesin.netcom.net.uk>

Doug Cowles wrote in 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.
>

Doug

Try this instead :-)
Apologies to the original author - the header got lost somewhere :-))

set pagesize 999
set linesize 100
column owner format a10;
column segment_name format a30;
column segment_type format a12;
column tablespace_name format a16;
column next_extent format 999,999,999;
prompt
prompt Database Objects that will have Trouble Throwing Extents prompt

SELECT seg.owner, seg.segment_name,

       seg.segment_type, seg.tablespace_name,
       t.next_extent
   FROM sys.dba_segments seg,
        sys.dba_tables   t

WHERE (seg.segment_type = 'TABLE'
  AND seg.segment_name = t.table_name   AND seg.owner = t.owner
  AND NOT EXISTS

            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  t.tablespace_name
                and bytes               >=  t.next_extent     ))

UNION
SELECT seg.owner, seg.segment_name,

       seg.segment_type, seg.tablespace_name,
       DECODE (seg.segment_type,
               'CLUSTER',  c.next_extent)
   FROM sys.dba_segments seg,
        sys.dba_clusters c
WHERE   (seg.segment_type = 'CLUSTER'
  AND    seg.segment_name = c.cluster_name
  AND    seg.owner        = c.owner
  AND    NOT EXISTS
            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  c.tablespace_name
                and bytes               >=  c.next_extent     ))

UNION
SELECT seg.owner, seg.segment_name,

       seg.segment_type, seg.tablespace_name,
       DECODE (seg.segment_type,
               'INDEX',    i.next_extent )
   FROM sys.dba_segments seg,
        sys.dba_indexes  i

WHERE (seg.segment_type = 'INDEX'
  AND seg.segment_name = i.index_name   AND seg.owner = i.owner
  AND NOT EXISTS

            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  i.tablespace_name
                and bytes               >=  i.next_extent     ))

UNION
SELECT seg.owner, seg.segment_name,

       seg.segment_type, seg.tablespace_name,
       DECODE (seg.segment_type,
               'ROLLBACK', r.next_extent)
   FROM sys.dba_segments seg,
        sys.dba_rollback_segs r

where (seg.segment_type = 'ROLLBACK'
  AND seg.segment_name = r.segment_name   AND seg.owner = r.owner
  AND NOT EXISTS

            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  r.tablespace_name
                and bytes               >=  r.next_extent     ))

/
prompt
prompt Segments that Are Sitting on the Maximum Extents Allowable prompt

select e.owner, e.segment_name, e.segment_type, count(*), avg(max_extents)  from dba_extents e , dba_segments s
where e.segment_name = s.segment_name
  and e.owner = s.owner
group by e.owner, e.segment_name, e.segment_type having count(*) = avg(max_extents)
/ Received on Tue Aug 17 1999 - 02:56:31 CDT