Skip to content

How To Create In Memory Jobs

APPLIES TO:

Oracle Database – Enterprise Edition – Version 12.2.0.1 and later
Information in this document applies to any platform.

GOAL

What are in-memory jobs and significance?

How many types of in-memory jobs exist?

How to create in memory jobs?

SOLUTION

What are in-memory jobs and significance?

In memory jobs use memory cache to reduce disk access and the time required for job creation and execution and can be used when many jobs

should be created and run during a short period of time.But may have slightly larger memory footprint.

These jobs are associated to the job_class DEFAULT_IN_MEMORY_JOB_CLASS, which has a logging level of NONE.

This will not generate logging information there by improving the performance.

How many types of in-memory jobs exist?

Two types of in-memory jobs are available: runtime (IN_MEMORY_RUNTIME) and full (IN_MEMORY_FULL).

1.In-memory runtime jobs are based on Lightweight Jobs, so they are persistent. They can have a repeat interval and run multiple times.

2.In-memory full jobs exist only cached in memory, so they are not persistent. They must have a program associated,

and they are meant to be run just once and discarded,so they cannot have a repeat interval.They do not have a backup on disk,

they do not generate redo in creation or at run time, greatly speeding their operation.

In RAC database they can be created in one of the RAC instances .

How to create in memory jobs?

 

create user test identified by test;
grant scheduler_admin to test;
conn test/<pwd>
create table jobtest(v_date date);

 

a. To create in memory job which runs multiple times

begin
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => ‘repeat_prog’,
program_type ‘PLSQL_BLOCK’,
program_action => ‘BEGIN insert into jobtest values (sysdate); commit; END;’,
enabled => true);
end;
/

 

select * from jobtest;

 

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘my_repeat_job’,
program_name => ‘repeat_prog’,
start_date => systimestamp,
repeat_interval => ‘freq=secondly;interval=10’,
job_style => ‘IN_MEMORY_RUNTIME’, <================(indicates job should run repeatedly)
enabled => true);
END;
/

b. To create in memory job which runs only once

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘my_immediate_job’,
program_name => ‘repeat_prog’,
job_style => ‘IN_MEMORY_FULL’, <===============( implies job will run once and discarded)
enabled => true);