Re: Determining amount of tablespace left

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

> >>

> >

> >

> >

>

>