1

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?

  • 1
    And this is why we have scheduler windows. You do not have to reinvent the wheel. – Balazs Papp May 10 '17 at 16:45
2

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

| improve this answer | |
  • 2
    @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. – JSapkota May 10 '17 at 13:37
  • @JSapkota,yes in Oracle 12c there is SQL_SCRIPT feature. – Md Haidar Ali Khan May 10 '17 at 13:39
  • @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}" – Bart Ch. May 10 '17 at 14:37
  • @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. – JSapkota May 10 '17 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.