You can create and schedule a job in Oracle by using certain conditions. The conditions can be when to run and procedure details. The frequency of the job you can set. Below is an example that you can use for your practice.
Note: The job scheduling you can do in SQL developer. Here are the steps.
1. Granting access to create a job
Here hr is the database instance.
grant create job to hr;2. Let HR see the V$SESSION table
grant select on v$session to hr;3. Crate a Table to hold user data
create table user_count (
number_of_users NUMBER(4),
time_of_day TIMESTAMP
)
TABLESPACE users;4. Create a stored procedure to hold data
CREATE OR REPLACE PROCEDURE insert_user_count AS
v_user_count NUMBER(4);
BEGIN
SELECT count(*)
INTO v_user_count
FROM v$session
WHERE username IS NOT NULL;
INSERT INTO user_count
VALUES (v_user_count, systimestamp);
commit;
END insert_user_count;
/5. Create a program for the job
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => ‘PROG_INSERT_USER_COUNT’,
program_action => ‘INSERT_USER_COUNT’,
program_type => ‘STORED_PROCEDURE’);
END;
/6. Enable the program
BEGIN
dbms_scheduler.enable(‘PROG_INSERT_USER_COUNT’)
END;7. Create a Scheduler
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => ‘my_weekend_5min_schedule’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=MINUTELY; INTERVAL=5; BYDAY=SAT,SUN’,
end_date => SYSTIMESTAMP + INTERVAL ‘30’ day,
comments => ‘Every 5 minutes’
);
END;
/8. Create a job with a schedule and program
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘my_user_count_job’,
program_name => ‘prog_insert_user_count’,
schedule_name => ‘my_weekend_5min_schedule’);
END;
/9. Enable the job
BEGIN
dbms_scheduler.enable(‘my_user_count_job’)
END;
/10. Verify job execution
select job_name, status, run_duration, cpu_used
from USER_SCHEDULER_JOB_RUN_DETAILS
where job_name = ‘MY_USER_COUNT_JOB’;11. Disable the job
BEGIN
dbms_scheduler.disable(‘my_user_count_job’)
END;
/12. Drop the job
BEGIN
dbms_scheduler.drop_job(‘my_user_count_job’)
END;Originally published at srinimf.com
