Building an Index ONLINE

Creating an index on a busy table may fail when user's DML is using the table (in 11G database).

SQL> CREATE INDEX owner.index_IDX1 ON owner.table_name(col1, clm_id, col2) TABLESPACE tablespace_name;
    *
ERROR at line 1:
 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index. Oracle will create an extra log table to keep track of any data changes without locking the table. There are restrictions on Online Index Building. One of them is that parallel DML is not supported during online index building. Oracle Database returns an error, if you specify ONLINE and then issue parallel DML statements.

Besides, other options, such as PARALLEL and NOLOGGING, will speed up the index creation.

SQL> CREATE INDEX owner.index_IDX1 ON owner.table_name(col1, clm_id, col2) TABLESPACE tablespace_name PARALLEL ( DEGREE 4 ) nologging online;
Index created.

With parallel, Oracle may automatically spawn parallel executions for every single SELECT statement. After that, the two options can be turned off by below statement:

SQL> alter index owner.index_IDX1 logging noparallel;

Extra notes: statement to turn on monitoring index usage (usually shall exclude PK from monitoring):

SQL> alter index owner.index_IDX1 monitoring usage ;