Home » SQL & PL/SQL » SQL & PL/SQL » how to check if job is running from different session
how to check if job is running from different session [message #276056] Tue, 23 October 2007 15:59 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Hi, I was wondring if anyone has ideas on how to go about this. I have a dbms_job that kicks off a process (PL/SQL procedure) that does some stuff. Our users can also run this process manually from the application. I wanted to put a check into my procedure to make sure that if this process is currently running from somewhere else, then dont run it. Here is the check:

SELECT
dj.what
FROM dba_jobs dj , dba_jobs_running djr
WHERE dj.job = djr.job
AND dj.WHAT LIKE 'myProcessName%';

Then I check if this brings something, then my process does nothing..And it wasnt working. And then it occured to me that (dumb me Smile this checks my session also, so its never going to work. Question - how do I check if this process is CURRENTLY running in the database by a different SESSION? Thank you!
Re: how to check if job is running from different session [message #276064 is a reply to message #276056] Tue, 23 October 2007 16:22 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
select count(*) 
from dba_jobs dj
where dj.what = 'KNOWN STRING'
 and dj.job IN (select djr.job 
                from dba_jobs_running
                where dj.job = djr.job
               )
/
Re: how to check if job is running from different session [message #276067 is a reply to message #276064] Tue, 23 October 2007 16:46 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
its doing the same as my query, am i wrong?

SELECT
dj.what
FROM dba_jobs dj , dba_jobs_running djr
WHERE dj.job = djr.job
AND dj.WHAT LIKE 'myProcessName%';

Re: how to check if job is running from different session [message #276120 is a reply to message #276056] Wed, 24 October 2007 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't check the jobs (what happens if 2 user want to execute the procedure and there's no job?).
Use DBMS_ALERT in your procedure to know if someoneelse is executing it.

Regards
Michel
Re: how to check if job is running from different session [message #276202 is a reply to message #276120] Wed, 24 October 2007 06:23 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
do u have an example of how this can be done? thank u!
Re: how to check if job is running from different session [message #276210 is a reply to message #276202] Wed, 24 October 2007 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/search?remark=quick_search&word=dbms_alert&tab_id=&format=ranked

Regards
Michel
Re: how to check if job is running from different session [message #276288 is a reply to message #276210] Wed, 24 October 2007 12:14 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
--pseudo code...
select count(*) into v_dummy from v$session where client_info = 'my job'; 

-- allow max of 1 copies of "my job"
if v_dummy > 0 then 
  raise_application_error(-20501, 'Job already running...');
else 
  --dbms_application_info.set_module( 'my-module', 'my-action'); 
  dbms_application_info.set_client_info( 'my job; 

  -- run job here

  -- done
  dbms_application_info.set_client_info( 'done' ); 
end if;
  exception
  when others then
    dbms_application_info.set_client_info( substr(sqlerrm, 1, 30) ); 
end;

[Updated on: Wed, 24 October 2007 12:15]

Report message to a moderator

Re: how to check if job is running from different session [message #276289 is a reply to message #276288] Wed, 24 October 2007 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I prefer dbms_alert, it is made for that.

Regards
Michel
Re: how to check if job is running from different session [message #276297 is a reply to message #276289] Wed, 24 October 2007 12:59 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
As far as I know, DBMS_ALERT requires the receiver to be connected to get the alert. A newly connected session will miss any alters previously sent - right?
Re: how to check if job is running from different session [message #276304 is a reply to message #276297] Wed, 24 October 2007 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think so, I think any session receive all signals as soon as it registers for it but I admit I didn't make test in this direction.
If this is the case, dbms_lock (which is used by dbms_alert) can be used and it is maybe a better solution for this topic as message that can send dbms_alert is of no use.

Regards
Michel
Re: how to check if job is running from different session [message #276308 is a reply to message #276304] Wed, 24 October 2007 14:39 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's an example of what can be done (follow the timestamps):
SQL> create or replace procedure myproc is
  2    h_lock varchar2(128);
  3    ret    pls_integer;
  4  begin
  5    -- getting a handle on the semaphore (lock)
  6    dbms_lock.allocate_unique ('my_semaphore',h_lock);
  7    -- checking and getting the semaphore
  8    ret := dbms_lock.request (h_lock, timeout=>0, release_on_commit=>false);
  9    case ret
 10      when 0 then -- semaphore free
 11        -- doing the job, here wait 30 seconds
 12        dbms_output.put_line(systimestamp||' - I''m doing the job...');
 13        dbms_lock.sleep (30);
 14        -- releasing the semaphore
 15        dbms_output.put_line(systimestamp||' - Finish, releasing the semaphore');
 16        ret := dbms_lock.release (h_lock);
 17      when 1 then -- semaphore busy
 18        dbms_output.put_line(systimestamp||' - Someone else is doing the job...');
 19      else 
 20        dbms_output.put_line(systimestamp||' - Error on semaphore: '||ret);
 21    end case;
 22  end;
 23  /

Procedure created.

SQL> set time on
21:35:03 SQL> -- session 1
21:35:03 SQL> exec myproc;
24/10/2007 21:35:03.671 +02:00 - I'm doing the job...
24/10/2007 21:35:33.671 +02:00 - Finish, releasing the semaphore

PL/SQL procedure successfully completed.

21:35:33 SQL> set time on
21:36:01 SQL> -- session 1
21:36:01 SQL> exec myproc;
24/10/2007 21:36:01.937 +02:00 - Someone else is doing the job...

PL/SQL procedure successfully completed.

21:36:01 SQL> set time on
21:36:10 SQL> -- session 1
21:36:10 SQL> exec myproc;
24/10/2007 21:36:10.234 +02:00 - I'm doing the job...
24/10/2007 21:36:40.234 +02:00 - Finish, releasing the semaphore

PL/SQL procedure successfully completed.

21:36:40 SQL> 

SQL> set time on
21:35:12 SQL> -- session 2
21:35:12 SQL> exec myproc;
24/10/2007 21:35:12.140 +02:00 - Someone else is doing the job...

PL/SQL procedure successfully completed.

21:35:12 SQL> set time on
21:35:19 SQL> -- session 2
21:35:19 SQL> exec myproc;
24/10/2007 21:35:19.734 +02:00 - Someone else is doing the job...

PL/SQL procedure successfully completed.

21:35:19 SQL> set time on
21:35:34 SQL> -- session 2
21:35:34 SQL> exec myproc;
24/10/2007 21:35:34.343 +02:00 - I'm doing the job...
24/10/2007 21:36:04.343 +02:00 - Finish, releasing the semaphore

PL/SQL procedure successfully completed.

21:36:04 SQL> 

Regards
Michel
Previous Topic: Long data types in Distinct or group by
Next Topic: Mutating Trigger..... Help Me
Goto Forum:
  


Current Time: Sun Dec 04 10:23:37 CST 2016

Total time taken to generate the page: 0.22178 seconds