Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Keeping having to rebuild Index - 10g

    Hi

    I created a table partitioned by datetime as:

    Code:
    CREATE TABLE TABLE1 (datetime date, col1 number, col2 number) NOLOGGING
    PCTFREE 60
    TABLESPACE ABC
    PARTITIONED BY (DATETIME)
    (PARTITION P19900101 VALUES LESS THAN (TO_DATE('1990-01-01 00:00:00,'SYYYY-MM-DD HH24:MI:SS, NLS_CALENDER=GREGORIAN'))
    
    ALTER TABLE SCHEMA.TABLE1 ADD (CONSTRAINT TABLE1_PK PRIMARY KEY (DATETIME, COL1) USING INDEX LOCAL TABLESPACE ABC_I;
    After creating it, I played around with the constraint on this table and now every day, I get:

    Code:
    'ORA 01502 - Index 'TABLE1_PK' or partition of such Index is in unusable state'.
    I then rebuild the Index like:

    Code:
    ALTER INDEX SCHEMA.TABLE1_PK REBUILD;


    and then I'm able to load data into the table. The issue is I keep having to rebuild the Index every day.

    Any ideas?

    Regards
    Shajju

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2013
    Posts
    1
    you must have some operation on your table to make it unusable..

    so..just keep rebuilding it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •