On occasion, it may be necessary to implement business logic
where, when one routine or business process is being run by a user, certain other
routine(s) should not be allowed to run and vice versa. This requirement may be
a functional or a technical need.
You may be able to relate to the following scenarios:
- A critical accounting process is triggered by a user. It
is necessary to avoid multiple runs of the same process at any given time. Therefore,
if one user has triggered the process, others should be prevented from doing
so. - Heavy data loading processes or data interfacing may
require that two routines do not run simultaneously. When a heavy upload is
triggered, simultaneous uploads from other sources should be prevented until
resources are available for use. - Several heavy-duty batch processes exist in the system
and proper resource sharing should be done. Running multiple batch processes may
slow down the system and eventually result in resource contentions. Preventing
such identified processes from running simultaneously is necessary. - Two or more processes carry out DML activities on common objects,
which may result in locking issues. This could give rise to errors like
0RA-00054 (resource busy) or ORA-00060(deadlock detected).
One solution would be to request that users avoid running
identified processes simultaneously; however, this is a manual control that can
be easily overlooked, requiring the DBA to kill the bad sessions to avoid database
congestion.
Using code, developers normally use flags or locks set in
some table that is referred to by all sessions. This will not work if the
session is killed or closed abnormally. Explicit coding needs to be done in
such cases to make the system foolproof; this could again amount to an
overhead.
The Oracle provided package, DBMS_APPLICATION_INFO, could be
used to satisfy this requirement, thus preventing the abnormal situations mentioned
above from arising.
Register the routine that is being run by calling
DBMS_APPLICATION_INFO.SET_MODULE. This tags the routine name in V$SESSION
dynamic view. Check for this information at the beginning of the concerned routines.
Once a routine is complete, unregister it. Note when a session is abnormally
closed or killed, V$SESSION is updated accordingly. There is no need of commit
or rollback or explicit updates.
Three columns, MODULE, ACTION and CLIENT_INFO can be set in
V$SESSION using DBMS_APPLICATION_INFO which could later be referred to, to
identify processes already running in the system. The following example sets
these three columns.
begin
dbms_application_info.set_module(module_name => ‘File Upload Process’,
action_name => ‘Uploading file’);
dbms_application_info.set_client_info(‘WDA001’);
end;
On running the above code, the three columns are set with
the value provided, and can be viewed from V$SESSION.
SQL> select module, action, client_info
2 from v$session
3 where sid = (select sid from v$mystat where rownum = 1);MODULE ACTION CLIENT_INFO
——————– ——————– ——————–
File Upload Process Uploading file WDA001
If direct access to the dictionary is not available, the set
data can be viewed using the same package.
declare
l_client_info varchar2(64);
l_module varchar2(48);
l_action varchar2(32);
begin
dbms_application_info.read_module(l_module, l_action);
dbms_application_info.read_client_info(l_client_info);dbms_output.put_line(l_module||’/’||l_action||’/’||l_client_info);
end;Output:
File Upload Process/Uploading file/WDA001
The data returned in the above case is only for the current
session. For our situation it is required that access be given to V$SESSION to
view all sessions data. Select privilege can be granted to the public so that
all logins have access to it.
SQL> grant select on v_$session to public;Grant succeeded.
Let’s look at an example. This is a very specific example but
generic code could be written along similar lines. AM_DEP1 and AM_DEP2 are
inter-dependent procedures and should not run simultaneously. The following function
checks to see if a particular procedure session is active and returns the
status accordingly.
create or replace function chk_dep_run (pi_proc in varchar2)
return boolean is
l_cnt pls_integer;
begin
select 1
into l_cnt
from v$session
where module = pi_proc
and rownum = 1;return(false);
exception
when no_data_found then
return(true);
end;
AM_DEP1 and AM_DEP2 will call the above function in the
beginning. Once the function confirms that the other procedure is not active,
the current routine will be registered and executed. Registering will tag the
necessary information in V$SESSION view.
create or replace procedure am_dep1 is
..
begin
if not chk_dep_run(‘AM_DEP2’) then
dbms_output.put_line(‘AM_DEP2 is currently running’);
return;
end if;dbms_application_info.set_module(‘AM_DEP1’, ‘running apps1’); –register
— continue executing the code.
..
dbms_application_info.set_module(null, null); –unregister
end;create or replace procedure am_dep2 is
..
begin
if not chk_dep_run(‘AM_DEP1’) then
dbms_output.put_line(‘AM_DEP1 is currently running’);
return;
end if;dbms_application_info.set_module(‘AM_DEP2’, ‘running apps2’); –register
— continue executing the code.
..
dbms_application_info.set_module(null, null); –unregister
end;
Conclusion
Use of DBMS_APPLICATION_INFO depends a lot on the coding.
Flexibility is provided to put any type of character string in the dictionary
table columns and then refer to it from the application. Proper coding
standards should be implemented for using this feature as per the application
requirement. Call the package at the beginning of all application sessions to
set the appropriate information. The package may be called multiple times to
update the dictionary during the lifetime of the session.
