Re: Extent sizes in LMT

You can do this, but there is a little bit of fiddling.

Rename the table (and its indexes) to something harmless.

Create a new partitioned table, defined so that the bottom partition exceeds the highest value in the current data set. If you want to use MAXVALUES then you would be best off with 3 partitions, so that future data goes into the second partition (with a reasonably forward boundary) and you have the option of splitting the top partition in the future when it is still empty. If you are sure you will never have to do such a split in the future, then stick to just two partitions.

Exchange the bottom partition with the
existing table, 'including indexes without validation'. Depending on the version of Oracle, you may need to set the PK constraints to disabled, all round to stop Oracle doing a massive PK search. (There is a note about this in my book in the chapters on partitions).

Experiment a bit with sql_trace and a couple of small data sets first to see what I mean.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Chuck Hamilton wrote in message <9fj0hb$4c5q1$1_at_ID-85580.news.dfncis.de>...


>Thanks to everyone for the replies.

>

>I was hoping to find a way to resize the extents without taking the table

>out of commission. It's a 10g table and I don't have another 10g of space

 to


>move it to. Here's an idea I had. Tell me if you think it's feasible. I

 want


>to convert the existing table into a range partitioned table based on a

>column that contains the date/time that the row was created. All old rows

 go


>into partition 1 (the original table). All new rows go into partition 2,

>which resides in a tablespace with larger extents. Can this be done with

>"alter table exchange..." command?

>