Job completion time in DBMS_JOBS

[user8177]

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..

Thanks in advance.

[Tim...]

Hi.

In 11g there is really no excuse to use DBA_JOB anymore. I think DBAs do it out of habit, or maybe lazyness. The new scheduler is very feature rich, but it can be used in quite a simple manner too.

http://www.oracle-base.com/articles/10g ... php#simple

The DBA_SCHEDULER_JOB_RUN_DETAILS view contains REQ_START_DATE, ACTUAL_START_DATE and RUN_DURATION columns, so it does pretty much everything you need.

http://www.oracle-base.com/articles/10g ... er-10g.php

In later versions of the database, it also includes an email notification system.

http://www.oracle-base.com/articles/11g ... tification

Regarding your specific issue, I don't think the information is tracked using the old package, so I would move to the new one ASAP.

Cheers

Tim...

[user8177]

Tim,
Thanks for your advise.
How do we migrate dbms_jobs to dbms scheduler? is it just disabling the dbms_jobs and creating the new dbms_scheduler jobs? Please share if there is any article on this?

Thanks in advance.

[Tim...]

Hi.

There is nt automatic migration process. Like you said, just disable/delete the old job and create a new one.

The links in my previous post show you how to use the new scheduler.

Cheers

Tim...

[user8177]

Hi Tim..
Is there are way to get the DDL of the DBMS_JOBS?

Thanks in advance.

[Tim...]

Hi.

You can pull the pieces out of the DBMS_JOBS view. The old style job definitions are pretty simple, so it's no major drama.

Cheers

Tim...

[user8177]

Thanks for your clarification.

I am testing this scenario.. I am recreating as a MV which is using DBMS_JOBS to refresh in another schema, Idealy i am trying migrate from DBMS_JOBS to DBMS_SCHEDULER..
Below is MV query (model)..

Code: Select all

CREATE MATERIALIZED VIEW "SYS"."CHECKLIST" ("ID", "I_DATE") ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSAUX" BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT sysdate+1 USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE AS
SELECT DISTINCT n2.name AS ID,SYSDATE AS I_DATE
FROM
(SELECT id,name,pencilviewpkg.getcontextname (id, 5976913, 200, NULL) AS nname
FROM admin_all WHERE subtypeid =
(SELECT id FROM admin_all WHERE nodetype = 't' AND name = 'Vendor'
)
) vend
INNER JOIN
(SELECT * FROM time_out WHERE present <> 12
) l1
ON l1.parentid = vend.id
INNER JOIN
(SELECT * FROM time_out WHERE present <> 12
) l2
ON l1.childid = l2.parentid
INNER JOIN
(SELECT * FROM time_out WHERE present <> 12 AND present <> 1382529
) l3
ON l2.childid = l3.parentid
INNER JOIN admin_all n
ON n.id = l2.childid
INNER JOIN admin_all n2
ON n2.id = l3.childid
WHERE pencilviewpkg.getcontextname (l1.childid, 5976913, 200, NULL) = 'Products';

I created this MV in SYS schema so that i can try dbms_scheduler to refresh the job.
I tried creating the job as below.. It failed. Looks like a syntax error but couldn't figure out.

Code: Select all

SQL> BEGIN
  2  DBMS_SCHEDULER.create_job (
  3  job_name => 'CHECKLIST',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN dbms_refresh.refresh('"SYS"."CHECKLIST"'); END;',
start_date => SYSTIMESTAMP,
  4    5    6    7  repeat_interval => 'freq=hourly; byhour=0; byminute=30; bysecond=0;'
  8  end_date => NULL,
  9  enabled => TRUE,
comments => 'CHECKLIST MV REFRESH JOB');
 10   11  End;
 12  /
job_action => 'BEGIN dbms_refresh.refresh('"SYS"."CHECKLIST"'); END;',
                                            *
ERROR at line 5:
ORA-06550: line 5, column 45:
PLS-00103: Encountered the symbol "SYS" when expecting one of the following:
) , * & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset

Also, When i checked the DBA JOBS..A new job was listed. I think it got created when i created the MV..Please clarify.

Code: Select all

  JOB LOG_USER        LAST                NEXT             FAIL        WHAT
----- --------------- --------------- --------------- ----- --------------------------------------------------
   51 CHECKVW        01/05/12 00:18  01/05/12 12:18      0 dbms_refresh.refresh('"CHECKVW"."CHECKLIST"');
   71 SYS                 01/05/12 06:31  02/05/12 06:31      0 dbms_refresh.refresh('"SYS"."CHECKLIST"');
   
 OWNER                          MVIEW_NAME                     LAST_REFRESH
------------------------------ ------------------------------ --------------------
CHECKVW                         CHECKLIST                        01/05/12 00:18
SYS                                  CHECKLIST                        01/05/12 06:31

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.

Thanks in advance.

[Tim...]

Hi.

"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.

Your action is this:

Code: Select all

'BEGIN dbms_refresh.refresh('"SYS"."CHECKLIST"'); END;'

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.

Code: Select all

'BEGIN dbms_refresh.refresh(''"SYS"."CHECKLIST"''); END;'

Cheers

Tim...

[user8177]

Tim,
Thanks.

I understand that we shouldn't use SYS but i was asked to and can't use another schema here.

Here is what i did.

After correcting the code..I tried creating a schedular job. I got error that it already exist. see below. I coundn't find any such jobs in dba_scheduler_jobs.

Code: Select all

SQL> BEGIN
  2  DBMS_SCHEDULER.create_job (
  3  job_name => 'CHECKLIST',
job_type => 'PLSQL_BLOCK',
  4    5  job_action => 'BEGIN dbms_refresh.refresh(''"SYS"."CHECKLIST"''); END;',
start_date => SYSTIMESTAMP,
  6    7  repeat_interval => 'freq=hourly; byhour=0; byminute=30; bysecond=0;',
  8  end_date => NULL,
  9  enabled => TRUE,
 10  comments => 'CHECKLIST MV REFRESH JOB');
 11  End;
 12  /
BEGIN
*
ERROR at line 1:
ORA-27477: "SYS.CHECKLIST" already exists
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at line 2

I could find one in DBA_JOBS..

Code: Select all

  JOB LOG_USER        LAST            NEXT             FAIL WHAT
----- --------------- --------------- --------------- ----- --------------------------------------------------
   51 CHECKVW        01/05/12 00:18  01/05/12 12:18      0 dbms_refresh.refresh('"CHECKVW"."CHECKLIST"');
   71 SYS            01/05/12 06:31  02/05/12 06:31      0 dbms_refresh.refresh('"SYS"."CHECKLIST"');
   
   OWNER                          MVIEW_NAME                     LAST_REFRESH
------------------------------ ------------------------------ --------------------
CHECKVW                        CHECKLIST                        01/05/12 00:18
SYS                            CHECKLIST                        01/05/12 06:31

I droped the job.

Code: Select all

SQL> EXEC DBMS_JOB.REMOVE(71);
PL/SQL procedure successfully completed.


SQL> select    job, log_user, to_char(last_date, 'dd/mm/yy hh24:mi') last,to_char(next_date, 'dd/mm/yy hh24:mi') next, failures fail,what from dba_jobs where   what like '%dbms_refresh%' ;

  JOB LOG_USER        LAST            NEXT             FAIL WHAT
----- --------------- --------------- --------------- ----- --------------------------------------------------
   51 CHECKVW        01/05/12 00:18  01/05/12 12:18      0 dbms_refresh.refresh('"CHECKVW"."CHECKLIST"');

But, even then, I am getting the same error while creating the scheduler job.

Code: Select all

SQL>    BEGIN
  2  DBMS_SCHEDULER.create_job (
  3  job_name => 'CHECKLIST',
  4  job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN dbms_refresh.refresh(''"SYS"."CHECKLIST"''); END;',
  5    6  start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byhour=0; byminute=30; bysecond=0;',
  7    8  end_date => NULL,
  9  enabled => TRUE,
 10  comments => 'CHECKLIST MV REFRESH JOB');
 11  End;
/
 12     BEGIN
*
ERROR at line 1:
ORA-27477: "SYS.CHECKLIST" already exists
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at line 2

I don't see this job in dba_objects aswell.

Am i missing something here? Please advise.

[Tim...]

Hi.

When creating the job you are trying to create an object called CHECKLIST, when one already exists. Change your job name to something like CHECKLIST_JOB and try again.

Cheers

Tim...

PS. Please use the code tags to format your code properly. Highlight the code (or formatted output) and click the "Code" button on the toolbar. It makes your post much easier to read. I've corrected your existing posts.