Re: How to deallocate inital extents from locally managed tablespace

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.

>> >