Re: 'drop extents'

"Stephan van Hoof" <test_at_test.nl> wrote in message news:1042711190.275442_at_newsreader1.wirehub.nl...
> Block size= 8kB
>
> I deleted all records from a table with 14 extents, size 30Mb.
> I analyzed the schema (compute).
> I inserted some records.
>
> When I run:
> SELECT (NUM_ROWS * AVG_ROW_LEN) bytes_used,
>
> (BLOCKS - EMPTY_BLOCKS) block_used
>
> FROM dba_tables
>
> WHERE table_name = 'WHITELISTS';
>
> The outcome is:
>
> bytes_used=537030
>
> block_used=3829
>
> Am I right to conclude:
>
> -There's still 3829*8kb=30Mb RESERVED space for the segement

Correct. Extents are only ever deallocated by a truncate, a drop or a 'deallocate unused' command. Never, ever by deletes.

> -The new records will first fill up till all 14 extents are occupied and
> after that will create a new next extend

Usually, yes. If you had multiple freelists for the segment, then things get a bit trickier. But by default, with a single freelist, then what you've written here is exactly what happens. Your existing extents/blocks are on the freelist, and fresh inserts will re-use that space first before running off to cause the acquisition of new ones.

>
> Is there a way to 'drop the extents' (I know export-import will do, but
> maybe there's another way?)
>

Truncate table BLAH; will free up all extents bar the initial (by default) or all extents bar the number specified by the MINEXTENTS parameter specified when the table was first created.

You could drop the original table and re-create it (but that would cause problems with object privileges and indexes needing to be re-defined, too). Nevertheless, a drop frees up all extents bar none.

Alternatively, 'alter table BLAH deallocate unused' frees up all extents/blocks beyond the table's High Water Mark (except that it gets more complicated than that in locally managed tablespace, or if you start using the 'keep xxM/K' syntax, or if you use the 'reuse' syntax, or if you fail to take account of the fact that the HWM is incremented in steps of 5 blocks, not one at a time.) Complications aside, you should free up lots of space by a simple, unadorned, 'deallocate unused'.

Regards
HJR
> Thanks
>
> Stephan
>
>
>
Received on Thu Jan 16 2003 - 04:10:08 CST