Re: How to deallocate inital extents from locally managed tablespace

I was hoping there was a slick way to get around pre-creating all the tables. It appears there isn't an overly-simple solution.

Off to do a little scripting to manually create the tables!

Thanks.

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 - 12:19:08 CST