Question :
I would like to set a job in the scheduler which will do a change of the resource manager plan.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'scott.dayplan',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'dayplan'; END;'
start_date => '17-MAY-10 01.00.00 AM Europe/Warsaw',
repeat_interval => 'FREQ=DAILY; BYHOUR=8;',
end_date => '99-SEP-04 01.00.00 AM Europe/Warsaw',
comments => 'Resource manager daytime plan');
END;
/
But the job_action
is given wrong. Any ideas how to execute alter system command in create job function?
Answer :
Create procedure to run your SQL statements then call that procedure in job_action
.
SQL> create or replace procedure my_proc
as
begin
execute immediate 'ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=DEFAULT_MAINTENANCE_PLAN';
end;
/
Procedure created.
SQL> EXEC dbms_scheduler.drop_job('job1');
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'job1',
job_type => 'PLSQL_BLOCK',
job_action => 'begin my_proc(); end;',
start_date => SYSDATE,
repeat_interval => 'FREQ = DAILY; INTERVAL = 1');
END;
/
PL/SQL procedure successfully completed.
SQL> EXEC dbms_scheduler.run_job('job1');
PL/SQL procedure successfully completed.
SQL> select log_date, status from dba_scheduler_job_run_details where job_name='JOB1';
LOG_DATE STATUS
------------------------------------- --------------
10-MAY-17 06.36.43.906003 AM -04:00 SUCCEEDED
Starting from Oracle 12c, we have SQL_SCRIPT
as job_type
which specifies that the job is a SQL*Plus script.
Moreover, if you just want to switch resource allocation plan, as Balaz Papp mentioned in the comment, you could use Scheduler Windows.
You create windows to automatically start jobs or to change resource allocation among jobs during various time periods of the day