Forum Stats

  • 3,769,313 Users
  • 2,252,947 Discussions


How to make index usable?

kalyan vedagiri
kalyan vedagiri Member Posts: 30 Red Ribbon
edited Jul 15, 2021 1:32PM in SQL & PL/SQL

While testing, I made the index of a table unusable. It has total 7 index partitions.

But I am unable to make the index usable again. After searching on internet tried rebuilding each partitions. But the last partition (which has 95% data ) throws an error saying to increase table space of temp. Now I don't know if I made a big mistake.

Could anyone help me to make index usable again ?



  • Sergei Krasnoslobodtsev
    Sergei Krasnoslobodtsev Member Posts: 495 Silver Badge
    edited Jul 15, 2021 2:45PM


    Rebuild partition index

    for example:

    alter table .....   modify partition .. rebuild unusable local indexes;


    -- gen sql online rebuild
    set linesize 5000;
    set pagesize 5000;
    set long 50000;
    set trimspool on
    set wrap on
    set termout off
    set verify off
    set feedback off
    set heading off
    set echo off
    select 'alter index '|| index_name ||' rebuild partition ' || partition_name ||' online ;' from user_ind_partitions;

    p.s. The documentation contains almost everything you need.

  • EdStevens
    EdStevens Member Posts: 28,525 Gold Crown

    When posting your question, consider:

    1) you did not show the actual, complete, exact SQL statement that resulted in some error.

    2) you did not show the actual, complete, exact error message that resulted. You only stated your paraphrase of it.

    no one here is looking over your shoulder. Don't make people guess. You get far better help if you use copy and paste to show exactly what you did and the exact error, in context. Preferably copy and paste of formatted code, not screen shots.

    That said, if your error message indicated a lack of temp TS (used for the sort operation needed to rebuild an index), why did you not simply follow up on that? What is the current size of your temp TS? What are the autoextend parameters for it?