adding a partition to a list partitioned table.

Hi,

I have created a table called movie_by_year which has list parttion based on the year
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 TABLESPACE HOLLYWOOD
  7 PARTITION BY LIST (Year)
  8 (PARTITION Y2002 VALUES ('2002'),
  9 PARTITION Y2003 VALUES ('2003'),
 10 PARTITION Y2004 VALUES ('2004')); Now I ahve to add a new partition y2005. So I did the following; .ALTER TABLE Movie_By_Year
  2 ADD PARTITION Y2005 VALUES('2005'); it says:
Table altered.
But if i try to add a movie of 2005 like; SQL> INSERT INTO MOVIE_BY_YEAR VALUES('H1', 'NNN', '2005', 'FICTION'); it gives me the folowing msg;
ERROR at line 1:
ORA-01502: index 'SYS.SYS_C005405' or partition of such index is in unusable
state.
sO i did the folowing;
SQL> ALTER TABLE Movie_By_year
  2 MODIFY PARTITION Y2005
  3 REBUILD UNUSABLE LOCAL INDEXES; Table altered.

Still I cannot add a movie by the year 2005, What shd I do..
Any help is apprecaited. Received on Mon Nov 14 2005 - 14:06:19 CST