Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    1

    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.

    Code:
        BEGIN DBMS_SCHEDULER.create_program ( program_name =>'statistics_prog3', 
                                                program_type=>  'PLSQL_BLOCK',
                                            program_action => 'DECLARE cdate date; fshared number;
        
          BEGIN
          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); 
        commit;
        END;',
        enabled =>TRUE);
        END;
    The next thing I did was to create the schedule.
    Code:
            BEGIN
            DBMS_SCHEDULER.create_schedule ( schedule_name => 'statistics_schedule3', 
        start_date => SYSTIMESTAMP, repeat_interval => 'freq=secondly;
        interval=60’,end_date => NULL, comments => 'Repeats minutely, for ever.'); 
            END;
    The next step was to create a job to run the program based on this schedule
    Code:
        BEGIN
        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 
        
        and schedule.'); 
        END;
    Next step is to run this job.
    Code:
    
        BEGIN
          DBMS_SCHEDULER****n_job (job_name            => 
        
        'STATISTICS_JOB3',
                                  use_current_session => 
        
        FALSE);
        END;
    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

    Code:
    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.

    Please help me in finding where it's going wrong.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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?

  3. #3
    Join Date
    Jul 2006
    Posts
    49
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •