Re: Problems with a subquery.

Well, assuming you mean just the table's segments, total allocated (not necessarily used) and number of indices by table ...

select table_name, count(*)
from dba_indexes
where table_name in (
  select segment_name
  from dba_segments
  where segment_type = 'TABLE'
  group by segment_name
  having sum(bytes) > 1000000)
group by table_name

Although, I still don't see what you are going to use this for. Remember, those indexes don't have anything to do with that 1,000,000 bytes.

Richard

Russ Brooks wrote:
>
> russ.brooks_at_dayzim.com (Russ Brooks) wrote in message news:<a7f817f2.0205031022.2b97ce89_at_posting.google.com>...
> No, what I'm trying to get is the number of indices, not the index
> names, for tables over 1,000,000 bytes.
> > Hi,
> > I'm having trouble with a character conversion coming out of a
> > subquery.
> > My query is:
> >
> > 1 select segment_name, bytes, index_name
> > 2 from dba_segments s, dba_indexes i
> > 3 where segment_type = 'TABLE'
> > 4 and bytes > 1000000
> > 5 and index_name = (select count(*)
> > 6 from dba_indexes
> > 7 where table_name = segment_name
> > 8 group by table_name)
> > 9* order by bytes desc
> >
> > If I execute this query I get an ORA-01722:invalid number on
> > index_name in line 5. I've tried putting an alias in the subquery and
> > to_number without success. Does anyone have any thoughts?
> >
> > TIA,
> > Russ
Received on Mon May 06 2002 - 12:31:01 CDT