When user tries to create a notebook he gets following Error

---------------------------
Error Creating New Collection
---------------------------
Sorry, an error occurred while executing the SQL "{ call Table_Section_Cell.Copy(?, ?, ?) }": ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE.
---------------------------
OK
---------------------------

Please note this was coming for a specific user only.

Investigation:

1. The first thing, I checked the position column in ELN_REFERENCES table for specific collection key. (In this case I took the collection key of user)
2. Please check in ELN_REFERENCES table if position column has value starting from 1 to n where is_active is 1.
select * from ELN_REFERENCES where COLLECTION_KEY=???? where IS_ACTIVE=1 order by position;

If the position column output does have any missing value/duplicate starting from 1 to n that means position is not correct somehow and needs to be corrected.
n=maximum value in position column

Solution

Please run the following script to correct this problem.


x=Please figure out what should be the correct position value we want to update.
y=Please find the row which has wrong position. please take the primary key of that row which we want to update.
z1=collection key which does have position issue.
z2=collection key which does have position issue. but we can remove this clause since primary key is unique. Since I run script in production hence I added this to be double sure as position gets updated at correct place.

*****************
DECLARE
skey number(12,0);
tzb number(10);
vkey number(10,0);

BEGIN

SELECT -1 * TO_NUMBER (SUBSTR (TZ_OFFSET (SESSIONTIMEZONE), 1, 1)
|| '1') * (60 * TO_NUMBER (SUBSTR (TZ_OFFSET (SESSIONTIMEZONE), 2, 2)) + TO_NUMBER (SUBSTR (TZ_OFFSET (SESSIONTIMEZONE), 5, 2)))
INTO tzb FROM DUAL;

start_session (skey, 1, tzb);

create_version(vkey, z1, 'enter the text as why we are updating position',0);

begin

update eln_references set position=x where primary_key=y and collection_key=z2;

end;
end_session(skey);
commit;
end;
/
show errors;
*********************

Imp Note/Disclaimer: This error message comes due to some other reason hence this solution might not work for all cases. Please do your own investigation and use this script only when there is problem with position column in ELN_REFERENCES only.