Overview

This document guides on the online option that allows performing DML on the table while creating an index.

By default, DML on the target table is not allowed during index creation.

However, when using the Online option, DML can be performed on the table during (re)creation of the index.

Caution

Index creation cannot be completed until the transactions on the original table are finished while creating online.

Scenario

Test Environment Setup

-- <session 1>
SQL> create table a (a number);
SQL> insert into a select level from dual connect by level <= 100000;
SQL> commit;
SQL> insert into a values (100001);   -- Trigger a transaction in session 1 and wait

Scenario 1. Index creation without online option

-- <session 1> Start a transaction on table a.
SQL> insert into a values (100001);

-- <session 2> Attempt to create index without online option fails.
SQL> create index idx_a on a (a);
TBR-12033: Lock acquisition failed in NOWAIT mode.

Scenario 2. Index creation with online option

-- <session 2> Create index with online option
create index idx_a on a (a) online;   -- Waits until transaction in session 1 is completed.

-- <session 1> Complete the transaction on table a.
commit;

-- <session 2> Creation completion notification
Index 'IDX_A' created.

Scenario 3. Incomplete termination during index creation with online option

-- <session 2> Statement canceled during index creation with online option
SQL> create index idx_a on a (a) online;
^C
Trying to cancel the current statement by user's request.

TBR-12040: Statement canceled.

-- <session 2> Retry index creation.
SQL> create index idx_a on a (a);
TBR-7102: Duplicate schema object 'TIBERO.IDX_A' exists.

SQL> create index idx_a on a (a) online;
TBR-7102: Duplicate schema object 'TIBERO.IDX_A' exists.

SQL> drop index tibero.idx_a;  
TBR-7249: Index 'IDX_A' is being built or rebuilt. -- Drop attempt also fails. Cleanup is required using DBMS_REPAIR.ONLINE_INDEX_CLEAN package.

Method

1. Execute DDL command ONLINE

CREATE INDEX index_name ONLINE;
ALTER INDEX index_name REBUILD ONLINE;

2. Temporary Segment creation and usage (internal operation)

Create (SORT) the index in Temp first.

3. Journal Table creation (internal operation)

Store table change data during index creation.

4. After Temporary Segment creation is complete, switch to Temp index and apply to Journal Table index (internal operation)

Caution

If online execution fails or is interrupted, you must perform cleanup of the index online-rebuild using the ONLINE_INDEX_CLEAN function of the DBMS_REPAIR package.

Note

If index creation with the online option is interrupted for some reason, the index will be created in an unusable state and the temporarily created journal table will remain.
Therefore, you must clean up using the DBMS_REPAIR.ONLINE_INDEX_CLEAN package.

If the following three query results exist, it can be judged that cleanup was not done properly.

SQL> select * from sys._dd_idxre;
    OBJ_ID    SGMT_ID PCTFREE_BYTES   INITRANS      FLAGS
---------- ---------- ------------- ---------- ----------
      4344       4347           819          2          2

SQL> select * from sys._dd_idxon;
    OBJ_ID    TYPE_NO      FLAGS
---------- ---------- ----------
      4344          1          2

SQL> select * from dba_objects where OBJECT_NAME like '%SYS_JOURNAL%';
OWNER           OBJECT_NAME                         SUBOBJECT_NAME        OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE          OBJECT_TYPE_NO CREATED              LAST_DDL_TIME        TIMESTAMP                 STATUS     TEMPORARY
--------------- ----------------------------------- -------------------- ---------- -------------- -------------------- -------------- -------------------- -------------------- ------------------------- ---------- ---------
SYS             SYS_JOURNAL_4344                                               4345           4345 TABLE                             1 2025/11/05           2025/11/05           2025-11-05:13:34:07       VALID      N

DBMS_REPAIR.ONLINE_INDEX_CLEAN

Performs cleanup for failed or interrupted index online-build or online-rebuild.

DBMS_REPAIR.ONLINE_INDEX_CLEAN
(
    object_id       IN  BINARY_INTEGER  DEFAULT ALL_INDEX_ID, -- Schema object ID of the index to clean up. When specified as ALL_INDEX_ID constant, cleanup is performed for all indexes.
    wait_for_lock   IN  BOOLEAN         DEFAULT LOCK_WAIT -- Lock mode when requesting DML LOCK on the table, partition, or subpartition related to the index. If set to LOCK_WAIT constant, it waits until the LOCK is acquired. If set to LOCK_NOWAIT constant, cleanup is immediately stopped if the LOCK cannot be acquired.
)  
RETURN BOOLEAN;

Example

DECLARE
    cleaned BOOLEAN;
BEGIN
    cleaned := false;
    WHILE cleaned = false
    LOOP
        cleaned := DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID,
                                               DBMS_REPAIR.LOCK_WAIT);
        DBMS_LOCK.SLEEP(10);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    RAISE;
END;
/  -- Perform cleanup for all indexes in LOCK_WAIT mode