DECLARE
    b       BOOLEAN;
    err     VARCHAR2(4000);
    l_count NUMBER := 0;
BEGIN
    FOR inst IN (SELECT id_institution
                   FROM institution
                  WHERE id_institution NOT IN (SELECT id_institution
                                                 FROM prof_institution
                                                WHERE id_professional = 142))
    LOOP
        b := pk_schedule.reset(inst.id_institution, 1, 1, err);
    END LOOP;

    FOR line IN (SELECT *
                   FROM schedule
                  WHERE id_instit_requested NOT IN (SELECT id_institution
                                                      FROM prof_institution
                                                     WHERE id_professional = 142)
                     OR flg_status = 'D')
    LOOP
        BEGIN
            DELETE FROM schedule
             WHERE id_schedule = line.id_schedule;
            IF l_count > 1000
            THEN
                l_count := 0;
                COMMIT;
            ELSE
                l_count := l_count + 1;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line(SQLERRM);
        END;
    END LOOP;
END;
/

create index sch_instit_flg_i on schedule(id_instit_requested, flg_status) tablespace index_l;

   UPDATE schedule s
           SET s.flg_status = 'D',
					 s.id_instit_requested = 56
         WHERE s.id_instit_requested = 19
           AND s.id_schedule NOT IN (2000, 2001, 2002, 2003, 413294)
					 and s.flg_status != 'D';




BEGIN
    dbms_mview.explain_mview('ALERT.MV_EPISODE_ACT_PEND_1'); 
    dbms_mview.explain_mview('ALERT.MV_EPISODE_ACT_PEND_2');
END;
/
--ver resultado do explain plan
SELECT mvname, capability_name, related_text, msgtxt
  FROM mv_capabilities_table
 WHERE msgtxt IS NOT NULL
   AND mvname LIKE 'MV\_EPISODE\_ACT\_PEND\_%' ESCAPE '\'
 ORDER BY 1, 2, 3;

--refrescar explicitamente as mviews
begin
dbms_mview.refresh(list => 'ALERT.MV_EPISODE_ACT_PEND_1', method => '?');
dbms_mview.refresh(list => 'ALERT.MV_EPISODE_ACT_PEND_2', method => '?');
end;
/

--apagar os mview logs
drop materialized view log on visit;
drop materialized view log on epis_type;
drop materialized view log on episode;
drop materialized view log on epis_info;

--criar os mview logs
create materialized view log on visit with rowid;
create materialized view log on epis_type with rowid;
create materialized view log on episode with rowid;
create materialized view log on epis_info with rowid;

--desactivar triggers e logs para fazer upgrade
alter trigger a_iud_visit_mv_epis disable;
alter trigger a_iud_epis_type_mv_epis disable;
alter trigger a_iud_episode_mv_epis disable;
alter trigger a_iud_epis_info_mv_epis disable;
drop materialized view log on visit;
drop materialized view log on epis_type;
drop materialized view log on episode;
drop materialized view log on epis_info;

--reactivar triggers e logs após upgrade
alter trigger a_iud_visit_mv_epis enable;
alter trigger a_iud_epis_type_mv_epis enable;
alter trigger a_iud_episode_mv_epis enable;
alter trigger a_iud_epis_info_mv_epis enable;
create materialized view log on visit with rowid;
create materialized view log on epis_type with rowid;
create materialized view log on episode with rowid;
create materialized view log on epis_info with rowid;




--ver log de erros
SELECT ltexte, to_char(ldate, 'yyyy-mm-dd hh24:mi:ss') ldate
  FROM alertlog.tlog
 WHERE lsection = 'MV_EPISODE'
   AND ldate > SYSDATE - 1
 ORDER BY ldate DESC;

--ver estado das mviews
SELECT mview_name,
       last_refresh_type,
       to_char(last_refresh_date, 'yyyy-mm-dd hh24:mi:ss') refresh_date,
       staleness,
       compile_state
  FROM dba_mviews
 WHERE mview_name IN ('MV_EPISODE_ACT_PEND_2', 'MV_EPISODE_ACT_PEND_1')
   AND owner = 'ALERT';

--ver config de refrescamento
SELECT VALUE, id_institution
  FROM alert.sys_config s
 WHERE s.id_sys_config = 'MV_EPISODE_ACT_REFRESH_TIMEOUT_SECS';

--ver estado dos mview logs
SELECT log_owner, master, log_table, rowids
  FROM dba_mview_logs
 WHERE log_owner = 'ALERT'
   AND master IN ('EPISODE', 'VISIT', 'EPIS_TYPE', 'EPIS_INFO');

--contar linhas nos mview logs
select count(0),'EPISODE'   from alert.mlog$_episode
union all
select count(0),'EPIS_TYPE' from alert.mlog$_epis_type
union all
select count(0),'EPIS_INFO' from alert.mlog$_epis_info
union all
select count(0),'VISIT'     from alert.mlog$_visit;

--limpar mviwe logs
begin
DBMS_MVIEW.PURGE_LOG('EPISODE', 2);
DBMS_MVIEW.PURGE_LOG('EPIS_TYPE', 2);
DBMS_MVIEW.PURGE_LOG('EPIS_INFO', 2);
DBMS_MVIEW.PURGE_LOG('VISIT', 2);
end;
/

--forçar criação de job
UPDATE episode SET flg_status = flg_status WHERE rownum < 2;
COMMIT;
SELECT job_name,
       to_char(start_date, 'yyyy-mm-dd hh24:mi:ss') start_date,
       enabled,
       state,
       run_count,
       max_runs,
       failure_count,
       max_failures,
       to_char(last_start_date, 'yyyy-mm-dd hh24:mi:ss') last_start_date
  FROM user_scheduler_jobs j
 WHERE j.job_name = 'UPDATE_EPISODES_MV';

--criar job explicitamente
BEGIN
dbms_scheduler.create_job(
    job_name   => 'UPDATE_EPISODES_MV',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'DECLARE invalid_state EXCEPTION;PRAGMA EXCEPTION_INIT(invalid_state, -04068);BEGIN pk_episode.update_mv_episodes_timeout();EXCEPTION WHEN invalid_state THEN pk_episode.update_mv_episodes_timeout();END;',
    start_date => SYSDATE + (1 / (24 * 60 * 60)),
    enabled    => TRUE,
    auto_drop  => TRUE);
END;
/
SELECT job_name,
       to_char(start_date, 'yyyy-mm-dd hh24:mi:ss') start_date,
       enabled,
       state,
       run_count,
       max_runs,
       failure_count,
       max_failures,
       to_char(last_start_date, 'yyyy-mm-dd hh24:mi:ss') last_start_date
  FROM user_scheduler_jobs j
 WHERE j.job_name = 'UPDATE_EPISODES_MV';