"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:a8khte$b1$1_at_lust.ihug.co.nz...
> Ooops. My mistake. The 10% for PCTFREE comes off what is left *after* the
> block header is accounted for. So the 10% should have been 729 bytes (call
> it 730). That means there's 8192-(730+88) available bytes per block (ie,
> 7374). That's 136 blocks or 1.11Mb.
>
> Sorry. It was late.
>
> Regards
> HJR
> --
> -----------------------------------------------
> Resources for Oracle : http://www.hjrdba.com
> ===============================
>
> "Morten" <morten_at_kikobu.com> wrote in message
> news:3CAD901E.9030208_at_kikobu.com...
> >
> > Wow. Thank you very much.
> >
> > Morten
> >
> > Howard J. Rogers wrote:
> > > 1. select sum(bytes) from dba_free_space where
> tablespace_name='BLAH';
> > >
> > > However, that's not quite what you are after, probably, since that
just
> > > shows you the total amount of free space in a tablespace -which could
be
> > > horribly fragemented into a zillion small pieces that nothing can
> actually
> > > make use of. So max(bytes) might be more up your street -the size of
the
> > > largest single piece of free space within a tablespace.
> > >
> > > 2. select blocks+empty_blocks from dba_tables where table_name='BLAH';
> > >
> > > "Blocks" are blocks below the high water mark (so they've been used at
> some
> > > point in the past, even if they happen to be empty now of any rows).
> "Empty
> > > blocks" are blocks above the high water mark, so they've never been
> used,
> > > but they are nevertheless allocated to the table, and thus count as
> > > belonging to that table.
> > >
> > > If you are after the amount of space that an idealized table would
take
> up,
> > > you want num_rows*avg_row_len from dba_tables. That gives you
something
> like
> > > the actual size of the data in the table. (say its 1000000 bytes). To
> work
> > > out what that ideally *could* be stored in, take your block size (say
> 8192
> > > bytes), take off about 88 bytes per block for the block header. Then
> take
> > > off PCTFREE (say 10% or 819 bytes). Thats 819+88 unusable bytes.
Leaving
> > > 7285 bytes per block of usable space. Therefore, 1000000 bytes divided
> by
> > > 7285 bytes per block yields 137.26 blocks... call it 138. 138 blocks
at
> 8192
> > > real bytes is 1.13Mb
> > >
> > > Incidentally, dba_tables is not populated with any of the required
> > > statistics until you've collected them by doing an analyze table blah
> > > compute [or estimate] statistics.
> > >
> > > Regards
> > > HJR
> > > --
> > > -----------------------------------------------
> > > Resources for Oracle : http://www.hjrdba.com
> > > ===============================
> > >
> > > "Morten" <morten_at_kikobu.com> wrote in message
> > > news:3CAD84AB.2070007_at_kikobu.com...
> > >
> > >>Hi. I'm about to create a large index, so I'm curious
> > >>whether there is a way to determine the amount of
> > >>tablespace left?
> > >>
> > >>Another question, any way to determine how much
> > >>space a table takes up? (other than doing a desc
> > >>and applying a little arithmetic)
> > >>
> > >>Thanks,
> > >>
> > >>Morten
> > >>
> > >
>
Query, If you were to rebuild the table or coalese the table, would it then
show close to that many blocks used and the rest as free blocks? (Or is
that number just an "idealized" number that the table will have as its
minimum?)
Received on Fri Apr 05 2002 - 10:44:34 CST
