-- Needs a user with DBA privileges
 CREATE TABLE "CMS_FRONTIER_MODS"."LAST_MODIFIED" 
   (	"TABLE_NAME" VARCHAR2(30 BYTE), 
	"TABLE_OWNER" VARCHAR2(30 BYTE), 
	"CHANGE_TIME" DATE, 
	 CONSTRAINT "LAST_MODIFIED_TIMES_PK" PRIMARY KEY ("TABLE_NAME", "TABLE_OWNER")
   ) 
 
  TABLESPACE "CMS_FRONTIER_MODS_DATA" ;


create or replace PROCEDURE                   KILL_OLD_JOBS_SC
IS
job_name varchar(100);
CURSOR kill_jobs IS select job_name from user_scheduler_jobs;
BEGIN
  OPEN kill_jobs;
  LOOP
    FETCH kill_jobs INTO job_name;
    EXIT WHEN kill_jobs%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(job_name);
    dbms_scheduler.drop_job (
    job_name =>job_name,
    force=>true
    );
  END LOOP;
  CLOSE kill_jobs;
  commit;
  END;
  
  
create or replace PROCEDURE  MOD_INFO_GATHERING_SC
IS
        inst NUMBER;
        jobno NUMBER;
        CURSOR refr_inst IS select INSTANCE_NUMBER FROM gv$instance order by 1;
BEGIN
        OPEN refr_inst;
        LOOP
                FETCH refr_inst INTO inst;
                EXIT WHEN refr_inst%NOTFOUND;
                --DBMS_JOB.SUBMIT(jobno,'REFRESH_MODS();',trunc(sysdate,'MI'),'trunc(sysdate,''MI'')+4/1440',FALSE,inst,FALSE);
                  dbms_scheduler.create_job (
            job_name => 'REFRESH_MODS_JOB'||inst||'',
            job_type => 'STORED_PROCEDURE',
            job_action => 'REFRESH_MODS',
            start_date => SYSTIMESTAMP,
            enabled => true,
            repeat_interval => 'FREQ=MINUTELY;INTERVAL=4'
            );

            DBMS_SCHEDULER.SET_ATTRIBUTE (
            name           =>   'REFRESH_MODS_JOB'||inst||'',
            attribute      =>   'instance_id',
            value          =>   inst);
        END LOOP;
        CLOSE refr_inst;
        --DBMS_JOB.SUBMIT(jobno,'UPDATE_MODS_TABLE();',trunc(sysdate, 'MI')+1/1440,'trunc(sysdate, ''MI'')+4/1440');
        dbms_scheduler.create_job (
            job_name => 'UPDATE_MODS_TABLE_JOB',
            job_type => 'STORED_PROCEDURE',
            job_action => 'UPDATE_MODS_TABLE',
            start_date => SYSTIMESTAMP+interval '1' minute,
            enabled => true,
            repeat_interval => 'FREQ=MINUTELY;INTERVAL=4'
            );

END;


create or replace PROCEDURE                                     REFRESH_MODS
IS
BEGIN
    dbms_stats.FLUSH_DATABASE_MONITORING_INFO; 
END;


create or replace PROCEDURE                                     UPDATE_MODS_TABLE
is
  mod_date DATE;
  tab_name varchar(30);
  sch_name varchar(30);
  chck NUMBER;
  last_mod DATE;
  
  
  CURSOR deletes is select table_name, table_owner from CMS_FRONTIER_MODS.LAST_MODIFIED where table_name !='ALL_TABLES' minus select table_name, owner from all_tables;
  CURSOR creates is select owner, object_name, created from all_objects where object_type='TABLE' and last_mod < created and owner like 'CMS_COND%';
  --CURSOR allTabs is select distinct table_owner from CMS_FRONTIER_MODS.LAST_MODIFIED;
 
  begin
    -- the time of last change recorded by earlier job - 5 seconds
    select NVL(max(change_time), to_date('19010101','YYYYMMDD'))-5/(24*60*60) into last_mod from CMS_FRONTIER_MODS.LAST_MODIFIED;
    
    -- table creations
   open creates;
    loop
      fetch creates into sch_name, tab_name, mod_date;
       EXIT when creates%NOTFOUND;
       SELECT count(1) into chck from CMS_FRONTIER_MODS.LAST_MODIFIED where table_name='ALL_TABLES' and table_owner=sch_name;
      if chck = 0 
      then
        insert into CMS_FRONTIER_MODS.LAST_MODIFIED values('ALL_TABLES', sch_name, mod_date);
      else
        update CMS_FRONTIER_MODS.LAST_MODIFIED set change_time=mod_date where table_name='ALL_TABLES' and table_owner=sch_name;
      end if;
      SELECT count(1) into chck from CMS_FRONTIER_MODS.LAST_MODIFIED where table_name=tab_name and table_owner=sch_name;
      if chck = 0 
      then
        insert into CMS_FRONTIER_MODS.LAST_MODIFIED values(tab_name, sch_name, mod_date);
      else
        update CMS_FRONTIER_MODS.LAST_MODIFIED set change_time=mod_date where table_name=tab_name and table_owner=sch_name;
      end if;
    end loop;
    close creates;
    
    -- updating mod dates  
    merge into CMS_FRONTIER_MODS.LAST_MODIFIED fm
    using(
      select timestamp, table_name, table_owner 
      from SYS.all_tab_modifications 
      where table_owner like 'CMS_COND%' and timestamp > last_mod) sm
    on (fm.table_name = sm.table_name and fm.table_owner=sm.table_owner)
    when matched then 
      update set fm.change_time = sm.timestamp
    when not matched then
      insert (fm.table_name, fm.table_owner, fm.change_time)
      values (sm.table_name, sm.table_owner, sm.timestamp);
  
      
    -- removing dropped tables
    open deletes;
    loop 
      fetch deletes into tab_name, sch_name;
      EXIT when deletes%NOTFOUND;
      delete from CMS_FRONTIER_MODS.LAST_MODIFIED where table_name=tab_name and table_owner=sch_name;
      SELECT count(1) into chck from CMS_FRONTIER_MODS.LAST_MODIFIED where table_name='ALL_TABLES' and table_owner=sch_name;
      if chck = 0 
      then
        insert into CMS_FRONTIER_MODS.LAST_MODIFIED values('ALL_TABLES', sch_name, sysdate);
      else
        update CMS_FRONTIER_MODS.LAST_MODIFIED set change_time=sysdate where table_name='ALL_TABLES' and table_owner=sch_name;
      end if;
    end loop;
    close deletes;

  commit;
end;