Re: script for table extent space used

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
Received on Tue Dec 17 2002 - 22:51:18 CST