Re: How to deallocate inital extents from locally managed tablespace

r_bauza_at_yahoo.com (rjb) wrote in message news:<772c2243.0203202321.6c7c49a4_at_posting.google.com>...
> How can you deallocate unused initial extents from a table created in
> a locally managed tablespace?
>
> For example: I have a LMT "new_tbs" with uniform extent size of 8k.
>
> Create table x(a number) storage (initial 1M) tablespace new_tbs;
>
> That gives me a table with 128 extents with no data in it.
>
> alter table x deallocate unused; does nothing....still 128 extents.
>
> truncate table x; does nothing....still 128 extents.
>
> Is there a way to get this thing down to 1 8k extent without dropping
> and re-creating with a different storage clause? (This is related to
> using import).
>
> Thanks in advance.

You have described the expected behavior: Oracle will allocate as many uniform extents as necessary to meet the initial extent allocation.

My question is why would you even build a tablespace with that small of a uniform extent size? Under the assumption that this is a test database and none of the tables will hold any real quantity of data then I think you should consider pre-allocating all your objects using 1 uniform extent size as the initial extent allocation. This will minimize your space usage, which seems to be your intent.

Even in test I think you should consider using 16K or 32K extents. To be honest I didn't realize you could set the extent size below 64K. We use uniform extents in production but chose 512k as our small object size and still allocate test object manually.

HTH -- Mark D Powell -- Received on Thu Mar 21 2002 - 08:43:49 CST