Re: Determining amount of tablespace left

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
>>