Can an Oracle user get a list of its own running sessions without access to v$session?

Question

I have an application that runs a process and I only want one process to run at a time. Some options are:

1. Use an object lock to prevent subsequent processes running.

   This would be fine, but I want the calling session to return immediately and not wait for the running session to complete.

2. Use a custom Y/N to set whether a process is running or not.

   I set a "Y" flag at the start of the process and set it to "N" when it finishes or fails. Also fine but feels like I'm re-inventing the wheel and doesn't feel like the way to go. It also falls short if the running session is killed as the flag stays at "Y".

3. Use dbms_application_info.set_module

   This approach seems the most robust, but if I'm to know there's an existing running process I think I need to be able to query v$session and I don't want this application to have such wide access.

Any ideas?

Answer 1

I would prefer #3 - if you don't want the user to have access to v$session, write the functionality you want into a package function owned by another schema and return the am_I_running status. Then you only have to grant EXECUTE on that function to the users of interest.

In production #1/#2 will usually involve some manual intervention when sessions fail or hang.

Answer 2

Option 4: use DBMS_LOCK to serialize access. Here is a link to the documentation: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10577/d_lock.htm#i1002556

An example:

First create an auxiliary procedure to serialize access to a certain procedure. The procedure uses the name of the procedure to generate a unique lockhandle. The 'NOTORA$' to make sure the lockname doesn't start with 'ORA$' as those are reserved for Oracle.

SQL> create procedure serialize_access (p_procedure_name in varchar2)
  2  is
  3    l_lockhandle varchar2(128);
  4    l_return     integer;
  5  begin
  6    dbms_lock.allocate_unique
  7    ( lockname   => 'NOTORA$' || p_procedure_name
  8    , lockhandle => l_lockhandle
  9    );
 10    l_return := dbms_lock.request
 11    ( lockhandle        => l_lockhandle
 12    , lockmode          => dbms_lock.x_mode
 13    , timeout           => 0  -- do not wait
 14    , release_on_commit => true
 15    );
 16    if l_return = 1
 17    then
 18      raise_application_error
 19      ( -20000
 20      , 'Someone else is running this procedure, so you''ll have to wait'
 21      );
 22    end if;
 23  end serialize_access;
 24  /

Procedure created.

In your procedure call this serialize_access procedure like this:

SQL> create procedure p1
  2  is
  3  begin
  4    serialize_access('p1');
  5    dbms_lock.sleep(30);
  6  end;
  7  /

Procedure created.

Where dbms_lock.sleep is used as a replacement for your real code. Now open up two or more other session and issue an "exec p1" command. The first session will start with waiting 30 seconds. The second session will show you this:

ERROR at line 1:
ORA-20000: Someone else is running this procedure, so you'll have to wait
ORA-06512: at "[schema].SERIALIZE_ACCESS", line 18
ORA-06512: at "[schema].P1", line 4
ORA-06512: at line 1

Hope this helps.

Regards, Rob.

Answer 3

The DBMS_LOCK answer is very nice but seems a bit complex... but that's one time setup complexity so it's fine.

A very simple way is to create a config table... could have a single column 'name'

When you want a lock do:

SELECT name INTO var FROM config WHERE name = 'lock_name' FOR UPDATE NOWAIT;

Trap for the error when it fails on NOWAIT.

The way you release that lock is with a COMMIT; or ROLLBACK; which is what your session will do if it dies (usually).

You can add to the table more rows if there are new single threaded procs that occur. You could even change the NOWAIT to WAIT n. That way the next instances could wait 10 seconds or whatever to see if it can get a lock. That would be the best way to keep one "Always up"