Execute ALTER SYSTEM command in oracle scheduler

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 1

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.

DBMS_SCHEDULER: Oracle 12c

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

Oracle Scheduler Concepts: Window