Re: count of obj

Here is something for you:

SQL> set timing on
SQL> select count(*) from dba_extents where bytes>1048576;  

  COUNT(*)

        43  

Elapsed: 00:00:17.99
SQL> select count(*) from (
  2 select owner,segment_name,segment_type,sum(bytes)   3 from dba_extents
  4 group by owner,segment_name,segment_type   5 having sum(bytes)>1048576);  

  COUNT(*)

        45  

Elapsed: 00:00:18.72
SQL> I don't have large database, so I took 1M as a limit. The first query takes into account partitioned tables, counting each partition as a separate segment, which is correct, but doesn't necessarily give us TABLES > 1M. Table can be larger then 1M, if it has 2 partitions, larger then 0.5M each. The second query doesn't take partitions into consideration and just counts extents. You'll notice that both queries take some time to execute. That is because in with locally managed extents, you have to ask each tablespace for the extent bitmap to decide how big did the things get. Difference between the two queries is negligable. Remember, extents are NOT maintained in the dictionary. Long execution of the "final frontier" queries is the price you pay for convenience.

On 03/06/2004 12:47:59 AM, "Kommareddy, Srinivas (MED, Wissen Infotech)" wrote:
> Thi also took more than 10 mins to execute and got only 1s (not the
> total count)
>
> 1
>
> COUNT(*)
> ----------
> 1
> 1
> 1
> 1
> 1
> 1
> 1
>
> 110 rows selected.
>
> but
>
> 1 select segment_name, sum(bytes) from dba_segments
> 2 group by segment_name
> 3* having sum(bytes)>1048576*1024
>
> this gave me 156 records
>
>
> checking the other one:
>
> 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) ;
>
> ..........
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jay
> Sent: Saturday, March 06, 2004 11:47 AM
> To: oracle-l_at_freelists.org
> Subject: RE: count of obj
>
> SQL> l
> 1 select count(*) from dba_segments
> 2 group by segment_name
> 3* having round(sum(bytes)/1024/1024/1024) >1
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Kommareddy, Srinivas
> (MED, Wissen Infotech)
> Sent: Friday, March 05, 2004 11:55 PM
> To: oracle-l_at_freelists.org
> Subject: count of obj
> 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
-----------------------------------------------------------------