7 Replies Latest reply: Apr 17, 2009 5:53 AM by 690297 RSS

    DBMS_SCHEDULER.CREATE_JOB

    690297
      Hello everyone!

      i have the following pretty simple scheduler job, which is just not working.
      basically, it should refreshes the Materialized View every minute. But it doesn't.
      After i execute DBMS_SCHEDULER.RUN_JOB('Schedule_Refresh_MView'); everytime new values are commited on the master table --> the refresh happens, and i retrieve the new values

      BEGIN
      DBMS_SCHEDULER.create_job (
      job_name => 'Schedule_Refresh_MView',
      job_type => 'PLSQL_BLOCK',
      job_action => 'BEGIN DBMS_MVIEW.REFRESH(''BERG.MVIEW_OBJBERG'',''?''); END;',
      start_date => SYSTIMESTAMP,
      repeat_interval => 'freq=minutely; interval=1',
      end_date => NULL,
      enabled => TRUE,
      comments => 'Refresh MView BERG.MVIEW_OBJBERG.');
      END;
      /

      i looked at the basic possible problems with Scheduling a job, and all is fine, eg. number of job_queue_processes, max_job_slave_processes, sessions.

      i work with Oracle 10.2.0.3 on windows (for testing) and Linux is my productive system.

      the job is created and listed in dba_scheduler_jobs.

      Am i missing something?
      thanks for any Tips.
      regards
      Sahba
        • 1. Re: DBMS_SCHEDULER.CREATE_JOB
          153119
          Quite often an interval of 60 seconds or less is too often.
          And refreshing an MV every minute looks pretty undesirable to me, especially when it is not a fast refresh.
          You would be better off with a database link.

          And,no, 'not working' does call for webcams and crystall balls, does it.
          Without any status info, or explicit error message, you would even better not post this at all.

          -----
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: DBMS_SCHEDULER.CREATE_JOB
            690297
            sybrandb,
            it seams, that you luck the ability to work with Oracle Server without ORA- message.
            if you can't analyse problems, then you would even better not post your reply at all.
            save your time and efforts next time, before you you attempt to reply.
            • 3. Re: DBMS_SCHEDULER.CREATE_JOB
              153119
              Sahba,

              When I run into problems I do analysis myself. As opposed to just dumping my problem in a forum of volunteers, and sitting back with my feet on my desk, waiting until the replies come in.
              It seems you are more talented in flaming, than setting up information systems properly. A 1 minute refresh interval is pretty daft, and likely the rest of your system is as clunky as this part, just because you refuse to read documentation, and play the role of 'DBA' with as little effort as possible.
              Maybe you should seek a profession where your talents to insult people, at the same time being lazy, is more appreciated.

              -----
              Sybrand Bakker
              Senior oracle DBA
              • 4. Re: DBMS_SCHEDULER.CREATE_JOB
                Chris Slattery
                Perhaps a quick review on asktom.oracle.com on how to present a failing use case might be of use to the OP
                • 5. Re: DBMS_SCHEDULER.CREATE_JOB
                  690297
                  how did you come up with all this nonsense... the forum is there to share usefull information and experience ... and most of the time, it gives just one hint that the other might have just misread.
                  never take others ideas as daft, because it imply how untalented, unexperianced you are.

                  i will forward your behaiviour to oracle forum managment, and soon you're no longer existent in this forum.

                  and for your information, i have been successful in sharing my experiance in this branch and the rewards were granted.
                  • 6. Re: DBMS_SCHEDULER.CREATE_JOB
                    690297
                    thanks Chris,
                    i think the user require some more privilegen granted, especially execute to be granted explicitly.
                    regards
                    • 7. Re: DBMS_SCHEDULER.CREATE_JOB
                      690297
                      hello,
                      in case someone is forced to use Windows OS and setup dbms_scheduler in DB, then make sure you start OracleJobSchedulerSID before OracleServiceSID or rebounce the DB.

                      regards
                      Sahba