Re: Determining amount of tablespace left

It'll be close, but yes, it's just an idealized number. It assumes that rows fit in the available block space perfectly. If uour row length was 680 bytes, for example, you can fit 10.84 rows in the 7374 available bytes -but Oracle won't split a row unless it absolutely has to, meaning only 10 rows will get stored. Thats 6800 bytes used, and 574 bytes wasted. So then the calculation is 1000000/6800=148 blocks, leading to 1.21Mb. And that assumes every row is exactly 680 bytes -some might be bigger, some smaller.

It also assumes a fixed size for the header of each block, which is false, as the header shrinks and grows according to the number of transaction slots it contains.

So no, it's impossible to know for sure how big the thing will actually be after a rebuild, but it gives a reasonable ball-park figure. The more you know your actual data, the more accurate you can get it, I guess.

Personally, I just buy more hard disks.

Regards
HJR

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

<SUSOTT_at_amnutrition.com> wrote in message
news:uarl3j2fiubc93_at_corp.supernews.com...


> "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?)

>
>