RE: add a partition to a range-partitioned table with local and g

Actually, it looks like you need to split the partition, since you are using GREATER THAN instead of LESS THAN. (So, Oracle thinks you already have a partition that can handle values greater than '01-JUL-2000')

You only need to drop the global indices, then you can rebuild the partitions on the local indices. Look in the user_ind_partitions table for all of the indices marked 'UNUSABLE' and issue alter index index_name rebuild partition partition_name statements for them.

-----Original Message-----
From: Crowe, Theresa Jenise [mailto:tecrowe_at_indiana.edu] Sent: Wednesday, August 16, 2000 2:07 PM To: Multiple recipients of list ORACLE-L Subject: add a partition to a range-partitioned table with local and globa

Hello Gurus,

I need to add a 2002 partition to a range-partitioned table in v8.1.6 on AIX RS6000 platform, v4.3.2. Our table has both local and global-partitioned indexes on it, and our fiscal year begins in July. I'm looking through the online documentation, and I think I need to drop all the indexes, use the syntax:

ALTER TABLE sales

      ADD PARTITION 2002 VALUES GREATER THAN ( '01-JUL-2000' )
      TABLESPACE mytablespace;

to add the new partition (last partition was 2001), then recreate the indexes.

Would it be possible for someone to verify this? Would it be better if I used the 'SPLIT PARTITION' syntax? Oracle says to use this when adding partitions to the "high" end, but I'm not sure if that means first or last. This table is in our data warehouse, and to me the "highest" partition would be the 2002 partition, and the lowest would be the 1994 partition. I did this so old data "purges" (data must be kept for 7 years) would just be a dropping of the lowest partition.

Last one: if I had a mix of local, global-partitioned and global indexes, would I be able to keep the global indexes and just drop the local and global-partitioned indexes (this is out of curiosity)?  

Thanks in advance!
Theresa Crowe
DBA in training : )

-- 
Author: Crowe, Theresa Jenise
  INET: tecrowe_at_indiana.edu

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).