Unanswered: Unable to run DBMS jobs based on a schedule
I am trying to learn DBMS jobs and scheduler.I tried writing a simple program that queries the v$sgastat view periodically and store the free shared pool memory value in another database. This program needs to run every minute and keep updating the 'tempstats' table with data.Here's what I've got so far.
BEGIN DBMS_SCHEDULER.create_program ( program_name =>'statistics_prog3',
program_action => 'DECLARE cdate date; fshared number;
select sysdate into cdate from dual;
select bytes/1024/1024 into fshared from v$sgastat where pool="shared pool" and name `enter code here`like "%free memory";
insert into tempstats values(cdate,fshared);
The next step was to create a job to run the program based on this schedule
DBMS_SCHEDULER.create_job ( job_name =>
'statistics_job3', program_name => 'statistics_prog3',
schedule_name => 'statistics_schedule3', enabled =>
TRUE, comments => 'Job defined by an existing program
Next step is to run this job.
DBMS_SCHEDULER****n_job (job_name =>
I executed the above blocks of code as 'sys' user with DBA permissions.I then run this query to see if the job is run or not
select * from DBA_SCHEDULER_RUNNING_JOBS;
but it did not return any rows and the 'tempstats' table is not getting updated.I did not get any errors when executing the code.All of them executed successfully.
I know you had to set job_queue_processes in your initSID.ora file for the old jobs system, but I am not sure about the new Scheduler system. What do you have your job_queue_processes init parameter set to?
The job will only appear in the "running" view if it is running at that exact time you check the view. Maybe the job has not started yet, or maybe it already finished and is waiting for the next interval. From looking at your job it should finish in less than 1 second, so it would be very hard to catch it in the running state. Try adding a call to dbms_lock.sleep(300) so you have time to catch it.