Re: How to deallocate inital extents from locally managed tablespace

It's a hypothetical situation to explain the real thing. I will not have 8k extent sizes in the real DB.

I'm importing a database which uses dictionary managed tablespaces. The initial extent on the imported tables is MUCH TOO LARGE (e.g. an initial extent of 10M when there is only 100k of data).

In the new database I'm dividing the tables into LOCALLY MANAGED TABLESPACES WITH UNIFORM EXTENT SIZES. The 3 tablespaces will have uniform extents sizes of 100k, 1M, and 10M.

The table that has 100k of data (but an initial extent of 10M for example), should go into the small_tbs (uniform extent size of 100k). However, this will create a table with a 1000 extents......998 of them which will be empty.

The question is: How can I import the tables with the overly large initial extents and reclaim the wasted space/extents.

Thanks again.

mark.powell_at_eds.com (Mark D Powell) wrote in message news:<178d2795.0203210643.5b1297ef_at_posting.google.com>...
> 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 - 11:58:25 CST