miércoles, 14 de septiembre de 2011

REFRESH VISTAS MATERIALIZADAS

create or replace
procedure dbo.ACT_VIEW_MATERIA
is
begin
FOR REG IN (SELECT OWNER,OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE LIKE'%MATERIA%' AND STATUS='VALID')LOOP
DBMS_MVIEW.REFRESH(REG.OWNER||'.'||REG.OBJECT_NAME,'C');
END LOOP;
end;


job

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"DBO"."ACT_VIEW_MATE"',
job_type => 'STORED_PROCEDURE',
job_action => 'DBO.ACT_VIEW_MATERIA',
repeat_interval => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=20;BYMINUTE=0;BYSECOND=0',
start_date => systimestamp at time zone 'America/Bogota',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'JOB ACTUALIZAR LA VISTAS MATERILIZADAS todos los sabados 8 pm',
auto_drop => FALSE,
enabled => TRUE);
END;