Re: Extent sizes in LMT

This is pretty much what I was going to experiment with. I do have a question or two about this however. I haven't found the answers in the manuals.

1. The old table has foreign key constraints on it. When I exchange the partition, will that apply the FKs to the new partitioned table, or just to the partition?
2. There is a secondary index on the old table that can't be equi-partitioned with the new partitioned table because it doesn't contain the partition key (sequential # PK) in the index. Will I need to make that a global partitioned index and rebuild it after the exchange? If so, that'll be a problem too as I don't have enough space to sort the index.

I'm wondering if I'm going at this the wrong way. I'm contemplating rewriting the nightly script so it uses some other method of determining whether or not a tablespace needs to be extended. I do keep daily size stats on each table and maybe I can just project the failure date based on the free space in the tablespace, and the average daily growth.

Thanks for all your help.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:991769621.18773.0.nnrp-07.9e984b29_at_news.demon.co.uk...
>
> 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?
> >
>
>
>
Received on Tue Jun 05 2001 - 15:11:12 CDT