Because if you take out the select line from the subquery and substitute
it with "select count", then "group by" will kick in for the count
function and you'll get number of extents for each object > 1G, which
is probably not what you want. Also, I prefer dba_extents (sys.ext$)
to dba_segments, because I'm used to dba_extents since the version 6,
when dba_segments view did not contain sizes. In a bi database, it's a
big query, nothing you can do about that.
On 03/06/2004 01:28:29 AM, Jay wrote:
> I don't understand why do we need such a complex query
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mladen Gogala
> Sent: Saturday, March 06, 2004 12:10 AM
> To: oracle-l_at_freelists.org
> Subject: Re: count of obj
>
>
> select count(*)
> ( from (select owner,segment_name,segment_type,sum(bytes)
> from dba_extents
> group by owner,segment_name,segment_type
> having sum(bytes)>1048576*1024
> )
> /
>
>
> On 03/05/2004 11:54:38 PM, "Kommareddy, Srinivas (MED, Wissen Infotech)"
> wrote:
> > Hi All,
> >
> > Can somebody give a simple query to find
> >
> > the count(*) of objects which have size >1 Gig
> >
> > its not this.
> >
> > select segment_name, round(sum(bytes)/1024/1024/1024) from dba_segmnets
> > group by segment_name
> > having round(sum(bytes)/1024/1024/1024) >1;
> >
> >
> > Need just count(*) of objects in the db > 1Gig size
> >
> > Tx and Regards,
> > Srinivas
> >
>
> --
> Mladen Gogala
> Oracle DBA
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
--
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Sat Mar 06 2004 - 00:43:12 CST
