How to create index on huge oracle table?

Question

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.

Answer 1

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;