Re: How to deallocate inital extents from locally managed tablespace

Thanks for the explanation on why the KEEP phrase works.

The reference I was using only mentioned the KEEP command releasing extents below MINEXTENTS, not INITIAL_EXTENT. The command releasing the extents above HWM and below INITIAL_EXTENT makes perfect sense.

Thanks Again.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1016740566.29585.0.nnrp-14.9e984b29_at_news.demon.co.uk>...
> Your problem is to do with where the
> highwater mark and initial_extent values
> are. To deallocate space below the
> initial_extent but above the highwater mark
> you will have to use the KEEP option, e.g.
>
> alter table small_table deallocate unused keep 16K.
>
> This should set a marker 16K above the current
> highwater mark, and the adjust initial_extent down
> to meet it (rounding, particularly for LMTs) as
> necessary.
>
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar - UK, April 3rd - 5th
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
>
> rjb wrote in message <772c2243.0203211019.427e9465_at_posting.google.com>...
> >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.
> >> >
Received on Thu Mar 21 2002 - 17:45:43 CST