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; /
job_action is given wrong. Any ideas how to execute alter system command in create job function?
Create procedure to run your SQL statements then call that procedure in
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
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