RE: How can I get information about space within a cluster
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <niall.litchfield@xxxxxxxxx>, <genegurevich@xxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 9 Dec 2006 13:51:01 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Niall Litchfield
Sent: Saturday, December 09, 2006 7:09 AM
To: genegurevich@xxxxxxxxxxxxxxxxxxxxx
Cc: Bobak, Mark; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxx
<mailto:genegurevich@xxxxxxxxxxxxxxxxxxxxx> <
genegurevich@xxxxxxxxxxxxxxxxxxxxx
<mailto:genegurevich@xxxxxxxxxxxxxxxxxxxxx> > 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@xxxxx <mailto:Mark.Bobak@xxxxx>
oquest.com <http://oquest.com> >
To
< genegurevich@xxxxxxxxxxxxxxxxxxxxx
<mailto:genegurevich@xxxxxxxxxxxxxxxxxxxxx>
12/08/2006 11:56 >, < oracle-l@xxxxxxxxxxxxx
<mailto:oracle-l@xxxxxxxxxxxxx> >
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@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto: oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx> ] On Behalf Of
genegurevich@xxxxxxxxxxxxxxxxxxxxx
<mailto:genegurevich@xxxxxxxxxxxxxxxxxxxxx>
Sent: Friday, December 08, 2006 11:46 AM
To: oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
