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
<[EMAIL PROTECTED]>
<lerobe@acxiom cc:
.co.uk> Subject: Partitions
Sent by:
[EMAIL PROTECTED]
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
1) Unload the data
2) Create my new tablespaces for the new partitions
3) Split the last partition
4) Repeat point 3 until I have my relevant number of smaller partitions
5) Create my local indexes for new partitions
6) 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
1) Unload the data
2) Create my new tablespaces for the new partitions
3) Split the last partition
4) Repeat point 3 until I have my relevant number of smaller partitions
5) Create my local indexes for new partitions
6) 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.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
