Re: Determining amount of tablespace left

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

>