Copying a table while maintaining consistency in Oracle

Question

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.

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.

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

--Use
DECLARE
   vLogon_Time ProcedureLock.Logon_Time%Type;
   eInUse      Exception;

   PRAGMA EXCEPTION_INIT (eInUse, -54);
BEGIN
   Begin
       SELECT Logon_Time INTO vLogon_Time FROM ProcedureLock FOR UPDATE NOWAIT;
   Exception
      When eInUse Then
         DBMS_Output.Put_Line('The procedure is running.');
         Return;
   End;


   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.');
      Return;
   End If;

   COMMIT;

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

   UPDATE ProcedureLock SET Logon_Time = NULL;
   COMMIT;
END;
/

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.