Oracle-L: RE: Partitions

From: Robertson Lee - lerobe <lerobe_at_acxiom.co.uk>
Date: Wed, 05 Jun 2002 08:38:44 -0800
Message-ID: <F001.004752AC.20020605083844@fatcity.com>

Thanks for that but we don't really have the spare space to set up the new tablespaces and the old one. Thats why I initially thought about unloading the data.

Thanks again

Lee

-----Original Message-----
Sent: 05 June 2002 16:31
To: ORACLE-L_at_fatcity.com
Cc: lerobe_at_acxiom.co.uk

Lee,

I've been doing a lot of work with reorging partitioned tables and splitting them.

I'm on Oracle version 8.0.4 and 2.6 of Sun Solaris.

I don't believe that you need to unload your data. You should be able to create your new tablespaces to the correct size. I believe you plan on creating on tablespace per partition, right. Just create those, including the new tablespace for the last, largest partition. Then split the partitions off one-by-one, specifying the new storage parameters and new tablespace name for each new partition in the split command. Your data will automatically be moved into the new tablespace. This is a nice little way to reorg your table. When you are finished splitting off all of your partitions, do an alter table move partition and move the remaining, large partition into it's new, smaller tablespace with the appropriate storage parameters. Then, drop your old tablespace once you have confirmed that it is empty.

This should work fine if you have enough space to have both the new and old tablespaces around at the same time.

There are some things to watch out for. Any global index will become invalidated. Any local indexes may have the closest partition become invalidated.
You may want to drop and rebuild your partitioned indexes into separate smaller tablespaces as well. You can also do an alter move on the indexes instead of dropping them. I know there are some open bugs on alter move of partitioned indexes, so check for those. I don't remember the specifics.

It's worth your time to get the sizes of the new tablespaces and initial and next extents right from the start. Also, you want to make sure you can get the move and the index rebuild done in the time available.

Been there, done that.

Cherie Machler
Oracle DBA
Gelco Information Network

                    Robertson Lee

                    - lerobe             To:     Multiple recipients of list
ORACLE-L <ORACLE-L_at_fatcity.com>     
                    <lerobe_at_acxiom       cc:

                    .co.uk>              Subject:     Partitions

                    Sent by:

                    root_at_fatcity.c

                    om

 

 

                    06/05/02 11:03

                    AM

                    Please respond

                    to ORACLE-L

All,

Oracle 8.0.5
Tru64 4.0f

We have a partitioned table here that has been left for sometime now and we need to split up the last partition into at least 6 to 7 resized partitions.

My take on it after RTFM ing was to

Unload the data
Create my new tablespaces for the new partitions
Split the last partition
Repeat point 3 until I have my relevant number of smaller partitions
Create my local indexes for new partitions
Reload the data

This "looked" OK but now I am rethinking this. The problem is that the last partition tablespace will still be massive, so I really need to drop the last partition, and its tablespace and recreate. Is this feasible ?? Can you
drop the last partition in a table (therefore leaving the second from last without a MAXVALUE).

Regards (and confused)

Lee

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system.
All,

Oracle 8.0.5
Tru64 4.0f

We have a partitioned table here that has been left for sometime now and we need to split up the last partition into at least 6 to 7 resized partitions.

My take on it after RTFM ing was to

Unload the data
Create my new tablespaces for the new partitions
Split the last partition
Repeat point 3 until I have my relevant number of smaller partitions
Create my local indexes for new partitions
Reload the data

This "looked" OK but now I am rethinking this. The problem is that the last partition tablespace will still be massive, so I really need to drop the last partition, and its tablespace and recreate. Is this feasible ?? Can you drop the last partition in a table (therefore leaving the second from last without a MAXVALUE).

Regards (and confused)

Lee

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Robertson Lee - lerobe
INET: lerobe_at_acxiom.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Jun 05 2002 - 11:38:44 CDT