How can I ensure that only one copy of a procedure is running in Oracle?
You can do this with DBMS_LOCK and an exclusive lock.

See the following procedure:

CREATE OR REPLACE PROCEDURE myproc
IS
  lockhandle VARCHAR2(128);
  retcode NUMBER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('myproclock',lockhandle);

  retcode:=DBMS_LOCK.REQUEST(lockhandle,timeout=&gt;0, lockmode=&gt;DBMS_LOCK.x_mode);

  IF retcode&lt;&gt;0
  THEN
    raise_application_error(-20000,'myproc is already running');
  END IF;

  /* sleep so that we can test with a 2nd execution */
  DBMS_LOCK.sleep(1000);

  retcode:=DBMS_LOCK.RELEASE(lockhandle);

END myproc;
/


Test (session 1):

SQL&gt; BEGIN
  2  myproc();
  3  END;
  4  /


(Obviously returns when DBMS_LOCK.sleep() returns).

Test (session 2):

SQL&gt; BEGIN
  2  myproc();
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-20000: myproc is already running
ORA-06512: at "PHIL.MYPROC", line 12
ORA-06512: at line 2


SQL&gt;


Obviously you need to GRANT EXECUTE ON DBMS_LOCK TO YOURUSER;.