JOB report

Le but  est de centraliser  dans  un table  tous les  jobs de la  journee  et  faire  un  diagramme de  GANTT   dans EXCEL


drop  table  MG.report_job ;
create  table   MG.report_job
(   id  number
  ,job_name varchar2(120)
  ,jour varchar2(20)
   , start_time varchar2(30)
   ,elapsed_second number --seconde,
   ,time_taken  varchar2(8)--16:18:00
   ,end_time varchar2(8)
   ,s varchar2(30)
);
alter  tableMG.report_job add  constraint  report_job_pk primary key (job,jour,start_time,elapsed_second);

CREATE  or  replace  SEQUENCE MG.REPORT_JOB_SEQ
  START WITH 2
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

CREATE DATABASE  link GTI CONNECT TO sysman IDENTIFIED BY  "XXCCVBGFHJKIYUTTRRRR"  USING 'HHHH';
Quand  il  est  prive seul    le user   qui  l  a créé  a l droit de  l utiliser


declare
  newid report_job.id%type;
CURSOR dataCursor IS
    SELECT  INPUT_TYPE  job_name
        ,to_char(trunc(start_time),'DD-MM-YYYY') jour
       ,TO_CHAR(START_TIME,'hh24:mi')      start_time 
       ,ELAPSED_SECONDS
       ,TIME_TAKEN_DISPLAY time_taken  --varchar2
       ,upper(STATUS)  STATUS
FROM V$RMAN_BACKUP_JOB_DETAILS  where  trunc(START_TIME)=trunc(sysdate -1 )
union
  select
    job_name
    ,to_char(trunc(req_start_date),'DD-MM-YYYY')  jour
   ,to_char(req_start_date,'hh24:mi')  start_time
   ,3600*extract(hour from run_duration)+ 60* extract(minute from run_duration)+ extract(second  from run_duration) --elapsed_seconds
     --,extract(hour from run_duration)||':'|| extract(minute from run_duration)||':'|| extract(second  from run_duration) --hour_minutes_second
     ,substr(to_char(run_duration,'hh24:mi:SS'),6,8)
     ,upper(STATUS) S
         from   sys.dba_scheduler_job_run_details
  where
  trunc(req_start_date)=trunc(sysdate -1)  
  and  to_number(to_char((24*extract(day from  run_duration))+(3600)*extract(hour from  run_duration)+
        (60)* extract(minute from  run_duration)+
        extract(second from  run_duration)))  >=60 --plus de 60 secondes
 union
 SELECT operation ||','||TARGET  job_name
        ,to_char(trunc(start_time),'DD-MM-YYYY')
       ,to_char(start_time,'hh24:mi')
       --,to_char(end_time,'hh24:mi')
    ,round((24*extract(day from (end_time-start_time) day(9) to second))+
       (3600)*extract(hour from (end_time-start_time) day(9) to second)+
        (60)* extract(minute from (end_time-start_time) day(9) to second)+
        extract(second from  (end_time-start_time) day(9) to second))  elapsed_seconds
      --,extract(hour from ('end_time-start_time','HH24'))||':'|| extract(minute from ('end_time-start_time','MM')))||':'||  extract(second  from ('end_time-start_time','SS')) Hour_min_sec
      ,substr((to_char((end_time-start_time),'hh24:mi:SS')),12,8)
      ,upper(substr(operation,1,6)||'_SUCCEED')
FROM dba_optstat_operations 
 where
 trunc(start_time)=trunc(sysdate -1) 
 and  to_number(to_char((24*extract(day from (end_time-start_time) day(9) to second))+(3600)*extract(hour from (end_time-start_time) day(9) to second)+
        (60)* extract(minute from (end_time-start_time) day(9) to second)+
        extract(second from  (end_time-start_time) day(9) to second)))  >=60  --plus de 60 secondes
       union    
 select job_name,
     to_char(trunc(start_time),'DD-MM-YYYY')
     ,to_char(start_time,'hh24:mi'),
 round((24*extract(day from (end_time-start_time) day(9) to second))+
       (3600)*extract(hour from (end_time-start_time) day(9) to second)+
        (60)* extract(minute from (end_time-start_time) day(9) to second)+
        extract(second from  (end_time-start_time) day(9) to second))  elapsed_seconds
   ,lpad(extract(hour from (end_time - start_time) day(9) to second) ,2,0)
           ||':'||lpad(( extract(minute from (end_time - start_time) day(9) to second)),2,0)
           ||':'|| lpad(extract(second from (end_time - start_time) day(9) to second),2,0)  hour
 ,upper(status)
 from sysman.MGMT$JOB_EXECUTION_HISTORY@GTI
 where
 trunc(start_time) =trunc (sysdate -1)
  and  to_number(to_char((24*extract(day from (end_time-start_time) day(9) to second))+(3600)*extract(hour from (end_time-start_time) day(9) to second)+
        (60)* extract(minute from (end_time-start_time) day(9) to second)+
        extract(second from  (end_time-start_time) day(9) to second)))  >=60
        and    target_name like 'RADPRD%'  order  by 3;
BEGIN
        FOR dataRow IN dataCursor
        LOOP
                SELECT report_job_seq.nextval INTO newid FROM DUAL;
                INSERT INTO report_job( 
                 id 
                ,job_name
                ,jour
                 , start_time
                 ,elapsed_second
                 ,time_taken 
                 --,end_time
                ,s
                 ) VALUES(newid,dataRow.job_name,dataRow.jour,dataRow.start_time,dataRow.ELAPSED_SECONDS,dataRow.time_taken,dataRow.STATUS);
        END LOOP;
END;
/
commit;