Oracle Live

11/04/2016

JOBS – Administracion

Filed under: Database — mogukiller @ 10:59 pm
Tags:
-- Habilitar / Deshabilitar jobs
-- Crear un job
-- Ejecutar un job de forma manual
-- Jobs de estadísticas
-- Jobs antiguos
-- Query: Jobs programados
-- WINDOWS - Info de las ventanas de mantenimiento
-- WINDOWS - Configuracion

-- Query: Jobs programados

COL PROGRAM_NAME FORMAT A30 
COL REPEAT_INTERVAL FORMAT A20 
COL OWNER FORMAT A20
COL "NEXT RUN" FORMAT A20
COL JOB_NAME FOR A30
SELECT OWNER,
  JOB_NAME,
  PROGRAM_NAME,
  REPEAT_INTERVAL,
  TO_CHAR(NEXT_RUN_DATE,'YY/MM/DD HH24:MI:SS') "NEXT RUN",
  ENABLED,
  STATE
FROM DBA_SCHEDULER_JOBS
ORDER BY 1,2
;
/*
OWNER                JOB_NAME                       PROGRAM_NAME                   REPEAT_INTERVAL      NEXT RUN             ENABL STATE
-------------------- ------------------------------ ------------------------------ -------------------- -------------------- ----- ---------------
ORACLE_OCM           MGMT_CONFIG_JOB                                               freq=daily;byhour=01 16/03/02 01:01:01    TRUE  SCHEDULED
                                                                                   ;byminute=01;bysecon
                                                                                   d=01

ORACLE_OCM           MGMT_STATS_CONFIG_JOB                                         freq=monthly;interva 16/04/01 01:01:01    TRUE  SCHEDULED
                                                                                   l=1;bymonthday=1;byh
                                                                                   our=01;byminute=01;b
                                                                                   ysecond=01

SYS                  BSLN_MAINTAIN_STATS_JOB        BSLN_MAINTAIN_STATS_PROG                            16/03/07 00:00:00    TRUE  SCHEDULED
SYS                  DRA_REEVALUATE_OPEN_FAILURES                                                                            TRUE  SCHEDULED
SYS                  FGR$AUTOPURGE_JOB                                             freq=daily;byhour=0;                      FALSE DISABLED
*/

-- Query: Detalle de la ejecucion de los jobs

COL JOB_NAME FOR A35
COL JOB STATUS FOR A10
COL REQ_DATE FOR A20
COL RUN_DURATION FOR A20
SELECT 
 LOG_ID, JOB_NAME, STATUS,
 TO_CHAR(ACTUAL_START_DATE,'DD/MM/YYYY HH24:MI:SS') REQ_DATE,      
 RUN_DURATION, ADDITIONAL_INFO       
 FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = '&job_name' AND ACTUAL_START_DATE >= SYSDATE -100 
 ORDER BY ACTUAL_START_DATE;

 /* 
     LOG_ID JOB_NAME                            STATUS                         REQ_DATE             RUN_DURATION
---------- ----------------------------------- ------------------------------ -------------------- --------------------
        45 ORA$AT_OS_OPT_SY_1                  SUCCEEDED                      25/02/2016 22:00:08  +000 00:00:18
*/

-- WINDOWS - Info de las ventanas de mantenimiento

col WINDOW_NAME for a20
col REPEAT_INTERVAL for a70
col DURATION for a30
SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION,ENABLED,ACTIVE FROM ALL_SCHEDULER_WINDOWS;

/* SALIDA
WINDOW_NAME          REPEAT_INTERVAL                                                        DURATION                       ENABL ACTIV
-------------------- ---------------------------------------------------------------------- ------------------------------ ----- -----
THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                  +000 04:00:00                  TRUE  FALSE
WEEKNIGHT_WINDOW     freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0  +000 08:00:00                  FALSE FALSE
WEEKEND_WINDOW       freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                    +002 00:00:00                  FALSE FALSE
WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                  +000 04:00:00                  TRUE  FALSE
TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                  +000 04:00:00                  TRUE  FALSE
SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                   +000 20:00:00                  TRUE  FALSE
SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                   +000 20:00:00                  TRUE  FALSE
MONDAY_WINDOW        freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                  +000 04:00:00                  TRUE  FALSE
FRIDAY_WINDOW        freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                  +000 04:00:00                  TRUE  FALSE
*/

-- WINDOWS - Configuracion
'nota: Cambiamos los atributos de la ventana, hora de comienzo y duracion'
BEGIN 
  DBMS_SCHEDULER.SET_ATTRIBUTE (
    'MONDAY_WINDOW'
,   'repeat_interval'
,   'freq=daily;byday=MON;byhour=05;byminute=0;bysecond=0'		<<<< Indicamos que empiece a las 05:00
);
END;
/

BEGIN 
  DBMS_SCHEDULER.SET_ATTRIBUTE (
    'MONDAY_WINDOW'
,   'duration'
,   '+000 03:00:00'					<<<< Indicamos que empiece a las 05:00
);
END;
/

'nota: Cerramos una ventana. Se pararan los jobs corriendo si tenemos stop_on_window_close a true'

BEGIN
	DBMS_SCHEDULER.CLOSE_WINDOW('MONDAY_WINDOW');
END;
/


-- Habilitar / Deshabilitar jobs

select 'execute dbms_scheduler.DISABLE(' || q'{'}' || a.owner || '.' || a.job_name || q'{'}' ||',TRUE);' as COMMAND 
from DBA_SCHEDULER_JOBS a
where a.enabled='TRUE'
UNION
select 'execute dbms_scheduler.ENABLE(' || q'{'}' || a.owner || '.' || a.job_name || q'{'}' ||');' as COMMAND 
from DBA_SCHEDULER_JOBS a
where a.enabled='TRUE';


-- Ejemplo para crear un job

SET LONG 100000
SELECT DBMS_METADATA.get_ddl('PROCOBJ','CREAR_PARTICIONES', 'PPCS') AS job_def FROM dual;


BEGIN
dbms_scheduler.create_job('SYSTEM.CREAR_PARTICIONES',
job_type=>'PLSQL_BLOCK', job_action=>
'DECLARE
           IDE pls_integer;
           BEGIN
           SYSTEM.PP_MANT_PARTS_PQ.MANT_PARTS (IDE);
          END;',
number_of_arguments=>0,
start_date=>NULL, repeat_interval=>'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=23;BYMINUTE=00;BYSECOND=00', 
end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', 
enabled=>FALSE, 
auto_drop=>TRUE,
comments=>'Proceso que crea las particiones necesarias para el mes siguiente al actual, de las tablas existentes en PP_CFGMANTPARTS'
);
dbms_scheduler.set_attribute('"CREAR_PARTICIONES"','logging_level',DBMS_SCHEDULER.LOGGING_RUNS);
COMMIT;
END;
/

-- Ejecutar un job de forma manual
exec DBMS_SCHEDULER.run_job (job_name=> 'EPPGA.ANURECARGASEDR', use_current_session => TRUE);

--Jobs de estadísticas
----------------------
--FAQ: Automatic Statistics Collection (Doc ID 1233203.1)
--Para desactivarlo

exec DBMS_AUTO_TASK_ADMIN.DISABLE(
 client_name => 'auto optimizer stats collection', 
 operation => NULL, 
 window_name => NULL);
 
--Para activarlo
exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);

--Para consultarlo
select client_name, JOB_SCHEDULER_STATUS  from DBA_AUTOTASK_CLIENT_JOB --where client_name='auto optimizer stats collection';
 
SELECT CLIENT_NAME,
       STATUS
FROM   DBA_AUTOTASK_CLIENT
--WHERE  CLIENT_NAME = 'auto optimizer stats collection';
 
--Jobs antiguos
---------------
col what for a50
set lines 150

select job,log_user,broken,what from dba_jobs where broken='N';

--desactivar
exec dbms_job.broken (1,true);
exec dbms_ijob.broken (1,true);
--commit;

--activar
exec dbms_job.broken (1,false);
exec dbms_ijob.broken (1,false);
--commit;

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: