Hi Tim,I am working on a script to monitor and send a daily alert with the status of the metrialized view refresh job that has been scheduled in dbms_jobs (FYI - Oracle 11.2.0.3). I see only Total_time in dba_jobs view where i had to note the value before and after job completion and get their difference to ind the duration of the job. I seethe start time of the job but not the endtime (runtime varies) to calculate duration. Thus here i am finding difficult to derive the logic.

Ideally i need to send the alert with start_time (say 10pm) and end_time (3.30am) of a job in an alert. Is there any view/table i can get the value from? Can you suggest a easier way to derive this logic?

Also, This is 11.2.0.3, Not sure why dba's prefer dbms_jobs over dbms_scheduler? Is there any reason behind this? I know dbms_scheduler is powerful than the dbms_jobs.. Please corrent me if i am wrong?

Incase, If we use dbms_scheduler, which view/col will give me this data, i mean the job completion time..

In this case, How will i move this job to scheduler. Do i need to specify anything in the MV create script? Ideally, I want to refresh the MV(CHECKVW) using scheduler to have better control and not with DBMS_JOBS.. Please advise and let me know if my approach is incorrect.

"I created this MV in SYS schema so that i can try dbms_scheduler to refresh the job."

What? You should never create objects in the SYS schema and you certainly don't need to in order to use the DBMS_SCHEDULER package. I don't understand what problem you think you have with using it for other users.

It contains single quotes, which you have not handled. As soon as the fist single quote is encountered, Oracle thinks the string has ended. When a string contains single quotes you must have two single quotes, so it knows this is a single quote in the string, rather than the end of the string. So what you really need is this.