26 July 2010

Finding current PL/SQL running as active jobs in the Oracle database

I was asked to kill a DBMS_JOB job process that was running a particular PL/SQL package/procedure. I was looking through documents and Oracle Support but didn't find anything useful, at least not during the couple of minutes I tried. So instead I had a look at v$session and realised that it includes the plsql_entry_object_id and plsql_entry_subprogram_id columns, which allows me to do the following:

select s.username, s.sid, s.serial#, s.program, s.last_call_et, s.logon_time, p.object_name package_name, p.procedure_name
   from v$session s, dba_procedures p
   where s.type='USER'
    and s.status='ACTIVE'
    and regexp_like(s.program, '\(J\d\d\d\)')
    and p.object_id (+) = s.plsql_entry_object_id
    and NVL(p.subprogram_id (+), 0) = NVL(s.plsql_entry_subprogram_id, 0)

Oh, and afterwards I found Oracle Support note 61730.1 and a number of other notes on the DBA_JOBS_RUNNING view, which states what jobs are currently running.


No comments: