how to execute CREATE INDEX without kill the sessions and skip error ORA-00054: resource busy and ac-CodePudding

I need to create some INDEXES in oracle database tables but always I got the error

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

I found in this question solutions to find out the sessions locked the table then kill the session but the table inserts each seconds a lot of results from interface sessions because this table interfaced with laboratory instruments and the machines insert laboratory results from the interface always ,

this is the solution I found :

https://stackoverflow.com/questions/4842765/ora-00054-resource-busy-and-acquire-with-nowait-specified-or-timeout-expired

but I cannot do it also I cannot stop the interface

Is there any other way to execute CREATE INDEX without kill the sessions or stop the interface ?

this is the DDL command :

CREATE INDEX  LAB_RESULTS_A_IDX3 ON   LAB_RESULT_STS
(HOSPITAL_NO, LAB_ORDER_NO, SAMPLE_NO, PROVIDING_RESOURCE, SERV_NO, 
END_RESULT)
LOGGING
TABLESPACE TRNG_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1181960K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
COMPRESS 1;

CodePudding user response：

If on the Enterprise Edition of Oracle did you specify the ONLINE parameter? Using Alex Poole's suggestion of changing DDL_LOCK_TIMEOUT and ONLINE if ONLINE does not work alone may get you by. Even ONLINE requires an exclusive lock at the end of the process. Earlier versions required an exclusive lock at both the beginning and end of the command.

CodePudding user response：

you can create index with ONLINE flag like below
this will not let the DMLs on the table block the index creation.

CREATE INDEX  LAB_RESULTS_A_IDX3 ON   LAB_RESULT_STS
(HOSPITAL_NO, LAB_ORDER_NO, SAMPLE_NO, PROVIDING_RESOURCE, SERV_NO, 
END_RESULT)
LOGGING
TABLESPACE TRNG_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1181960K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
COMPRESS 1 ONLINE ;

comment: since I can't add comments here is my response to Mark and Alex :1) yes ONLINE feature comes with enterprise edition of Oracle , I have been using the ONLINE feature since 11g .
The DDL_LOCK_TIMEOUT feature will not help the asker ,as this parameter will only delay the error nothing more, for short transaction holding the exclusive lock on the table his/her create index command will succeed eventually but for long running txn against the table the create index command will eventually fail. There are only two failsafe solutions to his problem either stop the application modifying the table or use the ONLINE flag with create index command.