Re: adding a partition to a list partitioned table.

mail2atulmehta_at_yahoo.com wrote:
> 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.

Where's the statement that created the index?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)