Skip to Main Content
  • Questions
  • Rebuilding the same index in multiple sessions(parallel)

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, karthik.

Asked: August 07, 2017 - 3:21 am UTC

Last updated: August 28, 2017 - 1:19 am UTC

Version: 11.0.2

Viewed 1000+ times

You Asked

Tried rebuilding the index in multiple sessions,but got the error

intiated rebuild in two sessions using below query

ALTER INDEX indexname REBUILD

One of the session is completed fine but other one failed with below error

ORA-08104: this index object 304713 is being online built or rebuilt
ORA-06512: at line 10
08104. 00000 - "this index object %s is being online built or rebuilt"
*Cause: the index is being created or rebuild or waited for recovering
from the online (re)build
*Action: wait the online index build or recovery to complete.

Please correct me if my understading is wrong about rebuilding indexes,Since alter table is a DDL it should wait to acquire the lock and proceed for second session

and Connor said...

Are you sure these were both just 'alter index rebuild' ?

You should only see ora-8104 during an *online* index rebuild.

If you're seeing this during a normal (offline) index rebuild, I'd be talking to Support, because they'll just serialise as you suspected.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for clarifying the query

karthik, August 11, 2017 - 2:24 pm UTC

Please find the below sequence of operations performed in multiple sessions simultaneously on same table

1.Truncate partition(each session will be truncating different partition)

2.rebuild index(Index is going to unusable state due to truncate partition)

3.insert rows into truncated partition and commit.

Gave 30 mins as ddl timeout for session and index rebuilt in offline mode.

In some sessions getting below ora error
ORA-08104: this index object 308307 is being online built or rebuilt

queries used

create table TEST_INDEX_REBUILD(
ID NUMBER,
P_KEY NUMBER PRIMARY KEY,
NOTE VARCHAR2(200),
CONSTRAINT TEST_INDEX PRIMARY KEY (P_KEY, ID)
)PARTITION BY LIST(ID)
(PARTITION P1 VALUES(1),
PARTITION P2 VALUES(2),
PARTITION P3 VALUES(3),
PARTITION P4 VALUES(4)
);


create or replace
PROCEDURE U_PROC_TEST_INDEX_REBUILD(PART IN NUMBER,V_STATUS OUT NUMBER)
AS
begin
DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
EXECUTE IMMEDIATE 'ALTER SESSION SET ddl_lock_timeout=1800';
DBMS_OUTPUT.PUT_LINE ('DELETE LIVE BEGIN ' || SYSTIMESTAMP);
EXECUTE IMMEDIATE 'ALTER TABLE TEST_INDEX_REBUILD TRUNCATE PARTITION P'||PART;
DBMS_OUTPUT.PUT_LINE ('DELETE LIVE END '|| SYSTIMESTAMP);
DBMS_OUTPUT.PUT_LINE ('REBUILD INDEX START'|| SYSTIMESTAMP);
EXECUTE IMMEDIATE 'ALTER INDEX TEST_INDEX REBUILD';
DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
INSERT INTO TEST_INDEX_REBUILD SELECT PART,ROWNUM,'PARTITION table' FROM large_table_yyy WHERE ROWNUM<20000001;
COMMIT;
DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
V_STATUS:=0;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE|| ' ERROR FOUND AT STEP '|| SQLERRM);
V_STATUS:=1;
END;

Executing the procedure using windows batch file to run simultaneously(part as 1,2,3,4),sometimes one of the session is failing with ORA-08104 error

Not sure why this is happening intermittently?

Kindly help me on this and let me know for further details.
Connor McDonald
August 28, 2017 - 1:19 am UTC

I've tried this test case multiple times on 11.2.0.4 and 12.2.0.1 and cannot get it to fail with the error you've provided.

I think its time to talk to Support.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database