0

I tried it with below SQL.

CREATE INDEX idx ON table1 (column1, column2) Local parallel 10;

but its giving me below error

SQL Error: ORA-12801: error signaled in parallel query server ..,
instance .. ORA-27090: Unable to reserve kernel resources for
asynchronous disk I/O Additional information: 3 Additional
information: 128 Additional information: -1374056344
12801. 00000 -  "error signaled in parallel query server %s"
*Cause:    A parallel query server reached an exception condition.
*Action:   Check the following error message for the cause, and consult
           your error manual for the appropriate action.
*Comment:  This error can be turned off with event 10397, in which
           case the server's actual error is signaled instead. 
Elapsed: 00:16:10.821

where table1 is a partitioned table.

  • Is it a partitionized table? – Wernfried Domscheit Mar 22 '18 at 12:32
  • yes it is partitioned. – Arpan Paliwal Mar 22 '18 at 12:32
  • What is the oracle version you are using 12c or 11g? – vishad Mar 26 '18 at 8:22
  • Did you try with a lower parallel value? e.g. parallel 4? – a_horse_with_no_name Sep 10 '18 at 5:51
8

You can try to build the index individually for each partition:

BEGIN
    FOR aPart IN (SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 MODIFY PARTITION '||aPart.PARTITION_NAME||' INDEXING OFF';
    END LOOP;   
END;
/

CREATE INDEX TABLE1 ON idx (column1, column2) LOCAL INDEXING PARTIAL;

BEGIN
    FOR aPart IN (SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX idx REBUILD PARTITION '||aPart .PARTITION_NAME||' PARALLEL 10';
    END LOOP;   
END;
/

-- Enable indexing, otherwise new partitions will not get indexed.
ALTER TABLE TABLE1 MODIFY DEFAULT ATTRIBUTES INDEXING ON; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy