Re: adding a partition to a list partitioned table.

<By adding the 2005 partition it rendered the primary key index (sys_c005405) unusable. >

Actually, this isn't true. I couldN'T reproduce the OP's problem, but below I received the same result against 9.2.0.6 and 10.1.0.3, both on Windows 2000...

SQL> CREATE TABLE Movie_By_Year
  2 (MovieID CHAR(4) PRIMARY KEY,
  3 Title varchar2(30) NOT NULL,
  4 Year CHAR(4),
  5 CATEGORY VARCHAR2(15))
  6 PARTITION BY LIST (Year)

  7     (PARTITION Y2002 VALUES ('2002'),
  8      PARTITION Y2003 VALUES ('2003'),
  9      PARTITION Y2004 VALUES ('2004'));

Table created.

SQL>
SQL> ALTER TABLE Movie_By_Year ADD PARTITION Y2005 VALUES('2005');

Table altered.

SQL> INSERT INTO MOVIE_BY_YEAR VALUES('H1', 'NNN', '2005', 'FICTION'); 1 row created.

SQL> Received on Wed Nov 16 2005 - 15:28:58 CST