RE: How can I get information about space within a cluster

hmm, well brute force you can get the table size data from the cluster by summing the vsizes of the columns of each row and grouping by the row block address if you want to see the block by block detail. That doesn’t properly account for individual rows spanning multiple blocks or out of band objects at the block level, but all y’all probably shouldn’t be messing with multiple table clusters including out of band objects anyway and simultaneously asking this question. Then again, you mentioned they are from an application not your design.

If you further sum up all the tables that are components of the cluster you can get the occupied space in each block (and therefore the free space if you know the block size and the overhead for your specific release and implementation).

You can skip the grouping by rba if you just want to know the occupied size per table of the cluster.

It’s a bit tedious, but if you want it you can get it (within the noted exceptions).

I haven’t tested what compute statistics does with table statistics that are members of clusters in too long to remember whether the result squares with the sum of sizing the details. In fact I *think* the last time I tried the stat collection blew up and I haven’t bothered since then.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Niall Litchfield
Sent: Saturday, December 09, 2006 7:09 AM To: genegurevich_at_discoverfinancial.com
Cc: Bobak, Mark; oracle-l_at_freelists.org
Subject: Re: How can I get information about space within a cluster

That's correct, you allocate space to the cluster, not to the individual tables within it. Hence my suggestion that you needed to look at DBA_SEGMENTS since storage is allocated to each segment whether table,index,cluster or whatever else I have forgotten about. On 12/8/06, genegurevich_at_discoverfinancial.com
<mailto:genegurevich_at_discoverfinancial.com> <
genegurevich_at_discoverfinancial.com
<mailto:genegurevich_at_discoverfinancial.com> > wrote:
Mark,

Thanks. It does help. Based on what you have said though it sounds like the most I can see is the space allocated
to the whole cluster, but I can not break it down by the table. Am I correct here?

thank you

Gene Gurevich

             "Bobak, Mark"
             < Mark.Bobak_at_il.pr <mailto:Mark.Bobak_at_il.pr>
             oquest.com <http://oquest.com> >
To
                                       < genegurevich_at_discoverfinancial.com


<mailto:genegurevich_at_discoverfinancial.com>

             12/08/2006 11:56          >, < oracle-l_at_freelists.org


<mailto:oracle-l_at_freelists.org> >

             AM                                                         cc

                                                                   Subject
                                       RE: How can I get information about
                                       space within a cluster

Space is allocated to a cluster, not to tables within the cluster. If you query on cluster_name, sum(bytes) where segment_type='CLUSTER', you'll see how much space is allocated to each cluster. To see what tables are in a given cluster, query on table_name from dba_tables where cluster_name = 'your cluster'.

Does that help?

-Mark

--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be
done at all.  -Peter F. Drucker, 1909-2005


-----Original Message-----
From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org>
[mailto: oracle-l-bounce_at_freelists.org


<mailto:oracle-l-bounce_at_freelists.org> ] On Behalf Of

genegurevich_at_discoverfinancial.com


<mailto:genegurevich_at_discoverfinancial.com>

Sent: Friday, December 08, 2006 11:46 AM
To: oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
Subject: How can I get information about space within a cluster

Hi everyone:

I have never dealt with oracle clusters. We have one app that utilizes
them and I was asked to calculate the space of the tables within a
cluster (the DB is oracle 10.2) - used and allocated. I have looked
through the dba_ tables , but couldn't find any data there. Does anyone
know how to accomplish this task?

thank you

Gene Gurevich


--
http://www.freelists.org/webpage/oracle-l






--
http://www.freelists.org/webpage/oracle-l





--
Niall Litchfield
Oracle DBA
http://www.orawin.info



--
http://www.freelists.org/webpage/oracle-l