Re: How to determine size used by an Instance...

HARI OM wrote:
> Thanks HJR and Ed for your reply.
>
> We would like to charge other agencies cost/MB for hosting their DB
> Instance on our Oracle Server (to save some hw, maintaince cost)and
> would like to find out better way to find the storage needs of their
> Oracle Data.
>
> What other parameters should we look into to come up with teh TOTAL
> SIZE Used by their data housed in our DB Server.
>
> Any related information on this is appreicated...
> Thanks again to Howard and Ed.
>
> HARI OM
>

We need to nail this down early on in the piece.

Do you really mean "the TOTAL SIZE used by their DATA"? (which this post says). Or "the TOTAL SIZE used by their dataBASE"? (which your last post said).

Because, as Ed pointed out, the two are quite different.

If it's the data*base*, then that's what my first answer has already told you.

If it's the actual data, then that's much more fiddly. You will have to do something like select sum(bytes) from dba_segments where OWNER = something that identifies each customer/agency with whom you are dealing. Then there's a question of whether things like packages and functions should be added in (DBA_OBJECT_SIZE), because code takes up space too. And what about histograms, dimensions and Lord knows what else...

And then there's the issue that DBA_SEGMENTS will, for example, report that my EMP table is 1MB in size, even though it has only 1 row in it. So do you want to measure the size of space *allocated* to objects, or just the space that's actually *consumed* by data within those objects. Because if it's the latter, you will need to calculate statistics for each of the objects and get the more precise results out of views such as DBA_TABLES and DBA_INDEXES (for example, a sum(avg_row_len*num_rows) from dba_tables).

Bear in mind that as well as tables, you will be storing indexes and clusters and partitions and materialised views and so on and on... and not all of them make it as easy to find out the difference between allocated and consumed space as tables do, for example. Good luck, for instance, with finding out the actual amount of space *consumed* by rows within a materialised view.... DBA_MVIEWS won't help. For those more 'exotic' segments, you may be compelled to deal only with allocated space -and then, perhaps, you will want to do the same for tables and indexes for the sake of consistency of treatment.

In short, the answer to your question depends entirely on *precisely* what your question means. You need to get precise about what it is that you want to measure. But hopefully you have a general idea now of where to look depending on the exact outcome of your 'be precise' exercise.

Regards
HJR Received on Mon Dec 20 2004 - 14:48:58 CST