Home » SQL & PL/SQL » SQL & PL/SQL » which dbms_job is running
which dbms_job is running [message #275389] Fri, 19 October 2007 13:40 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Is there a way to check if particular dbms_job is currently running if I have a job name, say "mydbmsjob"? Thank you!
Re: which dbms_job is running [message #275390 is a reply to message #275389] Fri, 19 October 2007 13:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
do any of the following look like possible candidates?

  1  select view_name from dba_views where view_name like '%JOB%'
  2* order by 1
SQL> /

VIEW_NAME
------------------------------
ALL_SCHEDULER_JOBS
ALL_SCHEDULER_JOB_ARGS
ALL_SCHEDULER_JOB_CLASSES
ALL_SCHEDULER_JOB_LOG
ALL_SCHEDULER_JOB_RUN_DETAILS
ALL_SCHEDULER_RUNNING_JOBS
AQ$SCHEDULER$_JOBQTAB
AQ$SCHEDULER$_JOBQTAB_R
AQ$SCHEDULER$_JOBQTAB_S
AQ$_SCHEDULER$_JOBQTAB_F
DBA_DATAPUMP_JOBS
DBA_JOBS
DBA_JOBS_RUNNING
DBA_SCHEDULER_JOBS
DBA_SCHEDULER_JOB_ARGS
DBA_SCHEDULER_JOB_CLASSES
DBA_SCHEDULER_JOB_LOG
DBA_SCHEDULER_JOB_RUN_DETAILS
DBA_SCHEDULER_RUNNING_JOBS
GV_$DATAPUMP_JOB
GV_$SCHEDULER_RUNNING_JOBS
KU$_JOB_VIEW
MGMT_JOB$EXECPLAN
USER_DATAPUMP_JOBS
USER_JOBS
USER_SCHEDULER_JOBS
USER_SCHEDULER_JOB_ARGS
USER_SCHEDULER_JOB_LOG
USER_SCHEDULER_JOB_RUN_DETAILS
USER_SCHEDULER_RUNNING_JOBS
V_$DATAPUMP_JOB
V_$RMAN_BACKUP_JOB_DETAILS
V_$RMAN_BACKUP_SUBJOB_DETAILS
V_$SCHEDULER_RUNNING_JOBS

34 rows selected.

Re: which dbms_job is running [message #275393 is a reply to message #275390] Fri, 19 October 2007 13:56 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
I have weird problem with this. If I run a select to identify currently executing dbms_job from sql plus or Toad, I get the expected result. If, I, however, copy that exact select into my pl/sql procedure, I get "table or view does not exist error". I have the following select that joins dbms_jobs and dbms_running_jobs to get what i need:

select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j

any idea why i cant have that in my pl/sql procedure?
Re: which dbms_job is running [message #275394 is a reply to message #275389] Fri, 19 October 2007 13:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Privs acquired via ROLE do NOT apply within PL/SQL procedures.
Explicit GRANT on Object must be issued.
Re: which dbms_job is running [message #275395 is a reply to message #275394] Fri, 19 October 2007 14:03 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Thank you!!!!!!
Re: which dbms_job is running [message #275396 is a reply to message #275394] Fri, 19 October 2007 14:06 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
is there any other way to check though without touching these dba tables? i doubt our dbas would give my procedure such grant...
Re: which dbms_job is running [message #275399 is a reply to message #275396] Fri, 19 October 2007 15:11 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ask him to create the procedure and grant you the privilege to execute it.

Regards
Michel
Previous Topic: Rouding issue
Next Topic: Pivot like this using DECODE???
Goto Forum:
  


Current Time: Fri Dec 09 01:41:18 CST 2016

Total time taken to generate the page: 0.12113 seconds