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?

• Balazs Papp: And this is why we have scheduler windows. You do not have to reinvent the wheel.

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

• atokpas: @BartCh. Sorry that I didn't notice that you are using Oracle 12c. You could use new feature, SQL_SCRIPT, see the documentation for details.
• Md Haidar Ali Khan: @JSapkota,yes in Oracle 12c there is SQL_SCRIPT feature.
• Bart Ch.: @JSapkota, There is also no dba_job_run_details table. But in documetation it is said "The job log includes the data dictionary views *_SCHEDULER_JOB_LOG and *_SCHEDULER_JOB_RUN_DETAILS, where: * = {DBA|ALL|USER}"
• atokpas: @BartCh.: My mistake, a typo, it should be dba_scheduler_job_run_details, in fact I typed the statement and copied the output from the terminal.

Answer 2

You didn't enable the job. Like that it will run only once execute dbms_scheduler.enable('JOB1');

Nice code though