How to create Oracle DBA Job, Scheduling and Monitoring the Status

In this Post we are going to explain how to create Oracle DBA Job.
First create a simple table and it contains two columns.



Create Statement for above table.

Create table ACtivity_logs(
ID number,
loggedDate Date
);

Then create the DBA jobs as below in the same schema user. 


PL/SQL script for above DBA job.

Begin
DBMS_SCHEDULER.create_job(
job_name        =>'My_First_Oracle_Job',
job_type        =>'PLSQL_BLOCK',
job_action      =>  'declare vcount Number:=0;
                    BEGIN
                    select count(*) into vcount from ACtivity_logs;
                    insert into ACtivity_logs values(vcount+1,sysdate);
                    COMMIT;
                    END;',
start_date      =>SYSTIMESTAMP,
repeat_interval =>'freq=SECONDLY;interval=5',
end_date        =>NULL,
enabled         =>TRUE,
comments        =>'My first DBA job');
END;

Then select the above created table and through the dba job it's data is populating.

select * from all_scheduler_job_log
where Owner = 'SYSTEM' AND job_name like 'MY_FIRST_ORACLE_JOB'
order by LOG_DATE;

Using Above query you can check the status of each execution  with the last execution time, next execution time and etc.

Thank you !!!!!