We have a PL/SQL stored procedure that is called on user demand which takes a copy of another table (ie. drop index > delete (not truncate) > insert > create index) and we've noticed the odd race condition where an index will not always get recreated.

This leaves us a table with no indexes, so future calls to the procedure fail (yes we could simply ignore the failed drop index). However, I'd like to get to the bottom of why the table is losing its indexes.

I don't think LOCK TABLE is the answer because after every DDL statement (eg. drop index) I'd keep losing the lock due to the implicit commit.

  • Why do you need to drop the index in order to copy the data? What exactly is that copy for?
    – user1822
    Dec 6, 2011 at 16:14
  • It's a full copy but there may be many rows different so if we dont drop/recreate the process slows due to indexing.
    – TownCube
    Dec 7, 2011 at 10:15

1 Answer 1


If you keep a separate table to maintain whether your procedure is running or not, then you only need to keep a lock as long as it takes you to update the table to ensure that another session is not doing the same. Here is some code to setup the table and then use it to run your procedure.

CREATE TABLE ProcedureLock AS (SELECT logon_time FROM v$session WHERE rownum<=1);
UPDATE ProcedureLock SET Logon_Time = NULL;

   vLogon_Time ProcedureLock.Logon_Time%Type;
   eInUse      Exception;

       SELECT Logon_Time INTO vLogon_Time FROM ProcedureLock FOR UPDATE NOWAIT;
      When eInUse Then
         DBMS_Output.Put_Line('The procedure is running.');

   UPDATE ProcedureLock SET Logon_Time = 
    (SELECT min(logon_time) FROM gv$session WHERE SID = SYS_CONTEXT('USERENV','SID'))
   WHERE Logon_Time IS NULL;

   If (SQL%ROWCOUNT <> 1) Then
      DBMS_Output.Put_Line('The procedure is running or failed to complete.');
   End If;


   DBMS_Output.Put_Line('The procedure is NOT running.');
   DBMS_Output.Put_Line('Run Procedure here.');

   UPDATE ProcedureLock SET Logon_Time = NULL;

First we lock the new ProcedureLock table and then update it. If either of these fail we know the procedure is running and therefore we should not run it again.

  • Thanks for the answer. How do you ensure this lock table only ever has one row at a given time? Do I need a primary key too? You mention "update" but use insert, can you explain further? Thank you.
    – TownCube
    Dec 6, 2011 at 11:17
  • Edited my answer. Dec 6, 2011 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.