Home » SQL & PL/SQL » SQL & PL/SQL » db jobs (Oracle 9i)
db jobs [message #351371] Tue, 30 September 2008 09:20 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

1.How to find the expected time to complete a db job.
lets say there is a db job 'jobab'. How to know what is the actual expected time to complete this job or actual elapsed time to complete this job.

2.How to find whether a job is failed or not

3.How to find whether a job is broken & released manually

Could you suggest any datadictionary that answers the above.

Reards,
Pointers.




.



Re: db jobs [message #351373 is a reply to message #351371] Tue, 30 September 2008 09:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
DBA_JOBS, DBA_JOBS_RUNNING
Re: db jobs [message #351376 is a reply to message #351371] Tue, 30 September 2008 09:40 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Can you please suggest how the above views answers question 1 and 3.
There is nothing like "expected end time" and about "released manully".But this answers about broken jobs for part of 3 rd question.

[Updated on: Tue, 30 September 2008 09:44]

Report message to a moderator

Re: db jobs [message #351377 is a reply to message #351376] Tue, 30 September 2008 09:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
There is no such thing as an expected end time, since the job has no idea what it's supposed to do.

Think about it..
Suppose you have a construct where in an if-then-else, the program needs to do a very very heavy query in the if-branch and do nothing in the else-branch.
If the outcome of the if-then is not known beforehand, how can anyone ever estimate the remaining time for the program?!
Re: db jobs [message #351380 is a reply to message #351376] Tue, 30 September 2008 10:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
These views suply you with (as far as I know) ALL of the information that is available inside the database.

You can check the Alert.Log to see if a Job has raised an unhandled exception, but that's it.

Re: db jobs [message #351381 is a reply to message #351380] Tue, 30 September 2008 10:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can work out if a job has Failed and is going to be re-run by comparing the Last_Date, Next_date and Interval. If the Next_Date is not the last_date + Interval, then the job has failed and is going to be re-run.
Re: db jobs [message #351389 is a reply to message #351371] Tue, 30 September 2008 10:31 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Thanks JRowbottom and Frank
Boath your points are really valid and helps me to go ahead.

Regards,
Pointers.
Re: db jobs [message #351512 is a reply to message #351371] Wed, 01 October 2008 04:57 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Can you suggest, how can I get the jobs which are broken and released manually..?

I can test for broken column. which only answers whether job is broken or not. But how to answer the jobs which are broken and released manually.

SQL> desc user_jobs;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------

 JOB                                       NOT NULL NUMBER
 LOG_USER                                  NOT NULL VARCHAR2(30)
 PRIV_USER                                 NOT NULL VARCHAR2(30)
 SCHEMA_USER                               NOT NULL VARCHAR2(30)
 LAST_DATE                                          DATE
 LAST_SEC                                           VARCHAR2(8)
 THIS_DATE                                          DATE
 THIS_SEC                                           VARCHAR2(8)
 NEXT_DATE                                 NOT NULL DATE
 NEXT_SEC                                           VARCHAR2(8)
 TOTAL_TIME                                         NUMBER
 BROKEN                                             VARCHAR2(1)
 INTERVAL                                  NOT NULL VARCHAR2(200)
 FAILURES                                           NUMBER
 WHAT                                               VARCHAR2(4000)
 NLS_ENV                                            VARCHAR2(4000)
 MISC_ENV                                           RAW(32)
 INSTANCE                                           NUMBER



Regards,
Pointers.
Re: db jobs [message #351516 is a reply to message #351512] Wed, 01 October 2008 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This information is NOT available.

Regards
Michel
Re: db jobs [message #351831 is a reply to message #351371] Fri, 03 October 2008 07:06 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
How can we add interval to last_date and compare with the next_date from the user_jobs table
SQL> desc user_jobs;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------

 JOB                                       NOT NULL NUMBER
 LOG_USER                                  NOT NULL VARCHAR2(30)
 PRIV_USER                                 NOT NULL VARCHAR2(30)
 SCHEMA_USER                               NOT NULL VARCHAR2(30)
 LAST_DATE                                          DATE
 LAST_SEC                                           VARCHAR2(8)
 THIS_DATE                                          DATE
 THIS_SEC                                           VARCHAR2(8)
 NEXT_DATE                                 NOT NULL DATE
 NEXT_SEC                                           VARCHAR2(8)
 TOTAL_TIME                                         NUMBER
 BROKEN                                             VARCHAR2(1)
 INTERVAL                                  NOT NULL VARCHAR2(200)
 FAILURES                                           NUMBER
 WHAT                                               VARCHAR2(4000)
 NLS_ENV                                            VARCHAR2(4000)
 MISC_ENV                                           RAW(32)
 INSTANCE                                           NUMBER


SQL> select last_date,this_date,next_date,interval from user_jobs where rownum<=
4;

LAST_DATE THIS_DATE NEXT_DATE INTERVAL          JOB
----------------------------------------------------------------------
02-OCT-08           03-OCT-08 sysdate + 1        41
03-OCT-08           03-OCT-08 sysdate + 1/1440   42
02-OCT-08           03-OCT-08 sysdate + 1        43
03-OCT-08           03-OCT-08 SYSDATE+5/1440     44


the below is working for job 41 but not working if it has some fraction value(for job=42 or 44)
select to_number(substr(INTERVAL,10))+sysdate from user_jobs where job=41

o/p:
TO_NUMBER
---------
04-OCT-08



Please suggest what needs to be done.

Regards,
Pointers.

Re: db jobs [message #351834 is a reply to message #351831] Fri, 03 October 2008 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is "working" but you don't display time.
Just read about TO_CHAR function.

Regards
Michel
Re: db jobs [message #351835 is a reply to message #351371] Fri, 03 October 2008 07:33 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Micheal could you please expatiate it...i dint get you...

Regards,
Pointers.
Re: db jobs [message #351842 is a reply to message #351835] Fri, 03 October 2008 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, misread what you posted.
You can't do it in pure SQL, you have to write a PL/SQL funtion that translates your string to a number.

Regards
Michel
Re: db jobs [message #351847 is a reply to message #351381] Fri, 03 October 2008 07:47 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
JRowbottom wrote on Tue, 30 September 2008 10:12
You can work out if a job has Failed and is going to be re-run by comparing the Last_Date, Next_date and Interval. If the Next_Date is not the last_date + Interval, then the job has failed and is going to be re-run.


Actually I am doing what JRowbottom has suggested....But facing lot of issues with the datatype of the "interval" column..

Could you suggest how can I do this.........

Re: db jobs [message #351848 is a reply to message #351847] Fri, 03 October 2008 08:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd do it using a format mask on the Last and Next dates that showed you the time, and then apply a MK I eyeball to the resulting dataset.

Re: db jobs [message #351849 is a reply to message #351371] Fri, 03 October 2008 08:09 Go to previous message
pointers
Messages: 410
Registered: May 2008
Senior Member
Could you do it with an example please.....Moreover what is MK..
I tried all the way. But dint get it....
Previous Topic: Wrap command
Next Topic: excecute a procedure
Goto Forum:
  


Current Time: Wed Dec 07 07:00:53 CST 2016

Total time taken to generate the page: 0.10310 seconds