Re: script for table extent space used

Howard J. Rogers wrote:
>
> Depends what you mean by 'used up'.
>
> For example, if you load 1 million rows and then delete 999,999 of them,
> technically you'll have used up a vast amount of space which nothing else
> can pinch, because the initial data load moved the 'High Water Mark' upwards
> as it proceeded. The HWM marks the furthest point to which any data in a
> table has ever gotten, and tells full table scans when to stop searching for
> further records (because, by definition, there cannot be any data above the
> HWM).
>
> Now if you discovered that your HWM was at the 99MB mark, that would mean
> 99MB of your table were reserved for the exclusive use of *that* table.
> Nothing else can make use of that space... even if most of it is fresh air
> as a result of you doing a bulky delete.
>
> So when you say 'how much is being used up', do you mean 'how much is
> reserved for the use of a table' or 'how much data have I actually got in
> the table'?
>
> If it's the first one, then you should investigate dbms_space, which will
> tell you the 'unused space' for a segment (though that's badly named: they
> mean 'never been used', which is different from 'was once used, but is now
> currently devoid of records, and is therefore unused').
>
> If it's the second one, then there's no easy answer. The simplest approach
> is perhaps to compute statistics for a table (alter table emp compute
> statistics), and then query dba_tables for that table. If you take the
> NUM_ROWS column and multiply by AVG_ROW_LEN (average row length) then that
> tells you how much actual table data is in the table currently.
>
> It doesn't tell you how much space that data is actually taking up, however.
> That's what BLOCKS tells you.
>
> If you hope to compare the two, then don't forget to allow 88 bytes for the
> block header and whatever your PCTFREE is (10, by default) which consitute
> unuseable space in a block.
>
> For example, if I had 1000 rows of 100 bytes average row length, that would
> imply 100,000 bytes (100K) of data. If I had an 8K db_block_size, then you
> might reasonably think that should be storable in 12.2-ish blocks (call it
> 13).
>
> However, since 88+10% (by default) of the 8192 bytes of a data block are
> unavailable, that actually means there are 8192-88=8104*0.9=7293 bytes of
> useable space in a block. Therefore the correct calculation is
> 100,000/7293=13.7 (call it 14) blocks.
>
> If your table has BLOCKS of 20, I'd suggest not worrying about it. If it had
> BLOCKS of 3000, then that means the HWM is way above where the data could
> actually fit, if only it were re-loaded compactly. That's time for a
> re-organisation.
>
> And the simplest way of reorganising a table to re-pack all its rows is
> alter table emp move; (provided you're on at least 8i).
>
> Regards
> HJR
>
> <Tim> wrote in message news:20iqvus62d5buj9ep35ggdkb383vc0fr2q_at_4ax.com...
> > Where can I get a script that will tell me how much of the extent in a
> > table (not tablespace) is being used, e.g. if I have a 6gb USER_1
> > tablespace which has a table named JOBS of only 1 extent of 100mb then
> > how much of 100mb is being used up? TIA

dbms_space has some goodies to assist with some of this info

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."