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.
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.