Назначенные задания. DBMS_SCHEDULER : Oracle

OlegON · █ 28.07.2011 11:02

Обращаю внимание, что существует механизм назначенных заданий dbms_scheduler (появился с 10ки, если не ошибаюсь).
Список заданий можно посмотреть

Код:

select * from dba_scheduler_jobs

вот пример определения заданий

Код:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'smpackupd_job',
job_type => 'PLSQL_BLOCK',
job_action => 'begin smpackupd; end;',
repeat_interval => 'freq=daily;byhour=06;byminute=30;bysecond=0',
enabled => TRUE
);
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'kill_old_sessions',
job_type => 'PLSQL_BLOCK',
job_action => 'begin kill_old; end;',
repeat_interval => 'freq=daily;byhour=22;byminute=29;bysecond=0',
enabled => TRUE
);
END;
/
create or replace procedure kill_old is
begin
  for c in (select sid, serial#
              from v$session
             where resource_consumer_group in
                   ('LOW_GROUP', 'DEFAULT_CONSUMER_GROUP', 'ANAL',
                    'OTHER_GROUPS')) loop
    begin
      execute immediate 'alter system disconnect session ''' || c.sid || ',' ||
                        c.serial# || ''' immediate';
    exception
      when others then
        null;
    end;
  end loop;
end;

-- предпоследний день месяца
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'mv_update_430',
job_type => 'PLSQL_BLOCK',
job_action => 'begin dbms_mview.refresh(''"FDC_EXEC"."FDC_MV_REPORT_430"''); end;',
repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=-2',
enabled => TRUE
);
END;
/

-- В окне, с прерыванием по его завершению
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'global_opt',
job_type => 'PLSQL_BLOCK',
schedule_name => 'WEEKEND_WINDOW',
job_action => '
begin
for c in (select owner,table_name from all_tables where owner not in (''SYS'',''SYSTEM''))
loop
begin
      execute immediate ''alter table ''||c.owner||''.'' || c.table_name || '' enable row movement'';
      execute immediate ''alter table ''||c.owner||''.'' || c.table_name || '' deallocate unused keep 0'';
      execute immediate ''alter table ''||c.owner||''.'' || c.table_name || '' shrink space cascade compact'';
      execute immediate ''alter table ''||c.owner||''.'' || c.table_name || '' shrink space cascade'';
      execute immediate ''alter table ''||c.owner||''.'' || c.table_name || '' deallocate unused keep 0'';
exception when others then null;
end;
end loop;
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => true, degree=>6);
end;',
enabled => TRUE
);
DBMS_SCHEDULER.SET_ATTRIBUTE( 
NAME => 'global_opt', 
ATTRIBUTE => 'STOP_ON_WINDOW_CLOSE', 
VALUE => TRUE
); 
END;
/
begin
dbms_scheduler.drop_job('GLOBAL_OPT');
end;

Пример создания scheduler_job

AirAir · █ 29.07.2011 11:36

включение/отключени
begin
dbms_scheduler.disable(Name => 'имя задания');
end;

если надо остановить выполняющиеся задания то
begin
dbms_scheduler.stop_job(Name => 'имя задания');
end;

если надо запустить задания то
begin
dbms_scheduler.run_job(job_Name => 'sys.sinsert');
end;
если надо запустить задания в фоновом режиме
begin
dbms_scheduler.run_job(job_Name => 'sys.sinsert'
use_current_session => false);
end;

если надо удалить задания то
begin
dbms_scheduler.drop_job(job_Name => 'sys.sinsert');
end;

Представления связанные с джобами
select * from DBA_JOBS_RUNNING
Select * from dba_scheduler_jobs
Select * from dba_scheduler_job_log
Select * from dba_scheduler_job_classes
Select * from dba_scheduler_job_run_details
Select * from dba_scheduler_window_log
Select * from dba_scheduler_window_details

OlegON · █ 12.08.2011 14:32

вот еще примерчики...

Код:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'kill_boring',
job_type => 'PLSQL_BLOCK',
job_action => '
for c in (select sid,serial# from v$session where type=''USER''
and lower(machine) not in (''oraclebi'',''rx'') 
and lower(username) in (''fdc'',''sys'')) 
loop 
begin 
 execute immediate ''alter system disconnect session '''''' || c.sid || '','' || c.serial# || '''''' immediate''; 
 exception when others then null; 
end; 
end loop;',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=1;',
enabled => TRUE
);
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'global_opt',
job_type => 'PLSQL_BLOCK',
job_action => '
begin
for c in (select owner,table_name from all_tables where owner not in (''SYS'',''SYSTEM''))
loop
begin
      execute immediate ''alter table ''||c.owner||''.'' || c.table_name || '' enable row movement'';
      execute immediate ''alter table ''||c.owner||''.'' || c.table_name || '' shrink space cascade'';
exception when others then null;
end;
end loop;
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => true, degree=>6);
end;',
repeat_interval => 'FREQ=WEEKLY; BYDAY=SAT; BYHOUR=10;',
enabled => TRUE
);
END;
/