Re: Extent sizes in LMT

You will have to drop the constraints from the old table and create them from the new table. If your version of Oracle is recent enough, you can create PK/FK constraints as disabled, then enable without validation, then validate them - this can speed things up.

However, your 'secondary' index seems to be a problem. A primary/unique key constraint on a partitioned table can only be represented by a locally partitioned index if it contains the partitioning columns - and I think you are saying that you have a meaningless PK on the table. (As a general rule, I advise against global indexes anyway on partitioned tables; in your case, with your motives it might not matter, but (a) it will be two bytes larger per row, and (b) you don't have the time / space to rebuild it.

It looks as if your short-term solution is to change your reporting mechanism.

--
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 <9fjegi$4g9pa$1_at_ID-85580.news.dfncis.de>...


>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?

>> >

>>

>>

>>

>

>