Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Database Sizing

Re: Database Sizing

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Wed, 21 Nov 2001 21:46:46 +1100
Message-ID: <3bfb8590$0$10228$afc38c87@news.optusnet.com.au>


select * from dba_free_space. That view only records space which is currently not reserved by any segment, and hence is genuinely unused. Take the sum of the bytes off from the sum of the sizes of all your datafiles, and that's genuinely allocated space.

That doesn't, however, deal with blocks which have been allocated to a segment and which are sitting there empty. The fact that a 100% empty block is part of an extent that has been nabbed by a segment means it doesn't get listed in dba_free_space. You could therefore play around with calculating statistics for all tables and then summing "empty_blocks" from dba_tables -that will give you allocated blocks which are 100% guaranteed empty because they lie above the high water mark (ie, never used by the segment to which they belong). But *that* doesn't tell you which blocks are below the high water mark yet sitting there empty (ie, allocated, once used, but subject to mass deletions and now empty). To get them.... forget it. There's no easy way to determine that last one (though you might compare average row length x number of rows with total number of (used) blocks to approximate it.

At the end of the day, a precise calculation is not possible, and the amount of work involved on the database (all that calculating of statistics for example) to get anything resembling a working approximation is going to be huge.

But have fun with it anyway!
Regards
HJR

--
Resources for Oracle: http://www.hjrdba.com
===============================


"Eileen Ong" <eileen_ong_at_abacus.com.sg> wrote in message
news:9tfs0o$q8j$1_at_newton.pacific.net.sg...

> Hi Allan,
>
> Thanks, but won't this also give the size of the unused space too? I only
> want to find out the size of all the database objects alone and do not
want
> to include the unused space.
>
> Cheers
> Eileen
>
> --
>
> "godmann" <allanwtham_at_yahoo.com> wrote in message
> news:95cd51c.0111210108.32de3438_at_posting.google.com...
> > Hi there,
> >
> > try select sum(bytes) from dba_segments where tablespace_name='xyz'.
> > If you have a lot of tablespaces, do a script for it.
> >
> > Allan W. Tham
> > DBA
>
>
Received on Wed Nov 21 2001 - 04:46:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US