Moving UK index on IOT to a new TS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Moving UK index on IOT to a new TS

  1. #1
    Join Date
    Dec 2001
    Posts
    203

    Moving UK index on IOT to a new TS

    Hello,

    I have an IOT table as defined below

    Table Definition

    create table temp
    (
    INV_NUM VARCHAR2(30) not null,
    INV_REF NUMBER(9) not null,
    constraint temp_PK primary key (INV_NUM)
    )
    organization index;

    alter table temp
    add constraint temp_UK2 unique (INV_REF)
    using index
    tablespace USERS
    );

    What I did?

    I moved the table temp to a tablespace as

    alter table temp move tablespace new_TS;

    successful

    What's going wrong?

    I tried to move the temp_UK2 index to the same TS but getting the following error, what is the solution?

    SQL> alter index temp_UK2 tablespace BF_temp rebuild online;
    alter index temp_UK2 tablespace BF_temp rebuild online
    *
    ERROR at line 1:
    ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

    Regards
    BB
    sumit

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    alter index index_name rebuild tablespace tablespace_name online;
    Thanks,
    Last edited by vnktummala; 02-18-2010 at 02:27 AM.
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Dec 2001
    Posts
    203
    SQL> alter index TEMP_UK2 rebuild BF_TEMP online;
    alter index TEMP_UK2 rebuild BF_TEMP online
    *
    ERROR at line 1:
    ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

    I think we need to recreate the index or probably thinking of reorganize the table in one go....
    sumit

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    alter index TEMP_UK2 rebuild tablespace BF_TEMP online;

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

Posting Permissions

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


Click Here to Expand Forum to Full Width