1 2 Previous Next 20 Replies Latest reply on Aug 11, 2009 3:34 PM by Hoek

    Error while running job which is in DBA_JOBS

    665881
      All,

      My environment is Oracle - 10.2.0.4.0
      Linux - 2.6
      2 Node RAC environment

      I have a job which is listed in DBA_JOBS. It is not auto executing. When I run manually, I am getting error.

      Please see the error details below.

      SQL> exec dbms_job.run(1);
      BEGIN dbms_job.run(1); END;

      *
      ERROR at line 1:
      ORA-12011: execution of 1 jobs failed
      ORA-06512: at "SYS.DBMS_IJOB", line 413
      ORA-06512: at "SYS.DBMS_JOB", line 275
      ORA-06512: at line 1


      When I check the alert log files, I am getting following message. Looks like some privilege issue. Can somebody help me. Job is owned by System and ran by System.


      Mon Aug 10 11:09:27 2009
      Errors in file ****************.trc:
      ORA-12012: error on auto execute of job 1
      ORA-01031: insufficient privileges
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
      ORA-06512: at "SYS.DBMS_IREFRESH", line 685
      ORA-06512: at "SYS.DBMS_REFRESH", line 195
        • 1. Re: Error while running job which is in DBA_JOBS
          Hoek
          Hi,


          What code are you exactly running? (from DBMS_JOB.SUBMIT).
          If you expect it to be auto executed, how did you define the interval?
          Looks like you're trying to refresh some materialized views?
          Job is owned by System and ran by System.
          That's usually a bad idea. Create your own user having the DBA role.
          I hope you're not trying to manipulate objects owned by system?
          1 person found this helpful
          • 2. Re: Error while running job which is in DBA_JOBS
            sb92075
            ORA-01031: insufficient privileges
            some sort of problem with privileges.

            REMEMBER - privileges acquired via ROLE do NOT apply within PL/SQL procedures; including ALL DBMS_JOBS invoked ones.
            1 person found this helpful
            • 3. Re: Error while running job which is in DBA_JOBS
              Hoek
              In addition to sb92075 (good point, sb!):

              http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html
              1 person found this helpful
              • 4. Re: Error while running job which is in DBA_JOBS
                665881
                Hoek

                Thanks for your message.
                Can you please tell me how to change PRIV_user,log_user and schema_user for this job in dba_jobs.

                I looked into dbms_job.change. i don't know how to change the value in dba_jobs. i.e, i dont know to change the user details for the job.

                Edited by: user1368801 on Aug 10, 2009 1:48 PM
                • 5. Re: Error while running job which is in DBA_JOBS
                  665881
                  Yes Hoek,

                  I am refreshing the materialized view.
                  What field in dba_jobs is
                  dbms_refresh.refresh(........);

                  log_user=SYSTEM
                  priv_user=SYSTEM
                  schema_user=SYSTEM

                  Now I want change from system to actual schema owner.

                  Can you please guide me
                  • 6. Re: Error while running job which is in DBA_JOBS
                    sb92075
                    i dont have privilege to change the user details.
                    what privileges DO you have?

                    Forgive me, but I am unclear exactly what you have or what you are trying to do.
                    I understand you have an error & you want it to go away.

                    Read this thread through your mother's eyes. Would she understand what you desire now?
                    1 person found this helpful
                    • 7. Re: Error while running job which is in DBA_JOBS
                      665881
                      Sorry for not making it clear.

                      In the DBA_Jobs table, when i queried, I noticed following values.

                      log_user=System
                      priv_user=system
                      schema_user=system

                      Actually, instead of system, it should be cust (schema name).

                      Basically I am trying to change the user details from system to cust.

                      I comeacross dbms_jobs.change procedure will help in changing the parameters for a job. But i dont know how to do

                      Please help
                      • 8. Re: Error while running job which is in DBA_JOBS
                        sb92075
                        PROCEDURE CHANGE
                         Argument Name               Type               In/Out Default?
                         ------------------------------ ----------------------- ------ --------
                         JOB                    BINARY_INTEGER          IN
                         WHAT                    VARCHAR2          IN
                         NEXT_DATE               DATE               IN
                         INTERVAL               VARCHAR2          IN
                         INSTANCE               BINARY_INTEGER          IN     DEFAULT
                         FORCE                    BOOLEAN           IN     DEFAULT
                        Above are the fields that can be "changed".
                        Please NOTE that OWNER is not listed.

                        You need to REMOVE the jobs as SYSTEM user & then SUBMIT as desired user.
                        • 9. Re: Error while running job which is in DBA_JOBS
                          665881
                          Hi

                          Ok, since we can't change the user, now I am submitting the job.
                          I am making some mistake in syntax. It is throwing me the error.

                          SQL> VARIABLE JOBNO NUMBER;
                          BEGIN
                          DBMS_JOB.SUBMIT(:JOBNO,'dbms_refresh.refresh("addr")','SYSDATE','SYSDATE + 1/1440');
                          COMMIT;
                          END;
                          SQL> 2 3 4 5
                          6 /
                          DBMS_JOB.SUBMIT(:JOBNO,'dbms_refresh.refresh("addr")','SYSDATE','SYSDATE + 1/1440');
                          *
                          ERROR at line 2:
                          ORA-01858: a non-numeric character was found where a numeric was expected
                          ORA-06512: at line 2


                          SQL> VARIABLE JOBNO NUMBER;
                          BEGIN
                          DBMS_JOB.SUBMIT(:JOBNO,'dbms_refresh.refresh('addr')','SYSDATE','SYSDATE + 1/1440');
                          COMMIT;
                          END;SQL> 2 3 4
                          5 /
                          DBMS_JOB.SUBMIT(:JOBNO,'dbms_refresh.refresh('addr')','SYSDATE','SYSDATE + 1/1440');
                          *
                          ERROR at line 2:
                          ORA-06550: line 2, column 47:
                          PLS-00103: Encountered the symbol "addr" when expecting one of the
                          following:
                          ) , * & | = - + < / > at in is mod remainder not rem => ..
                          <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
                          LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
                          The symbol ", was inserted before "addr" to continue.
                          • 10. Re: Error while running job which is in DBA_JOBS
                            sb92075
                            01858, 00000, "a non-numeric character was found where a numeric was expected"
                            // *Cause: The input data to be converted using a date format model was
                            //  incorrect.  The input data did not contain a number where a number was
                            //  required by the format model.
                            // *Action:  Fix the input data or the date format model to make sure the
                            //  elements match in number and type.  Then retry the operation.
                            • 11. Re: Error while running job which is in DBA_JOBS
                              665881
                              Submit job worked.

                              Following is the syntax I used.

                              VARIABLE JOBNO NUMBER;
                              BEGIN
                              DBMS_JOB.SUBMIT(:JOBNO,'dbms_refresh.refresh(''addr'');','SYSDATE','SYSDATE + 1/1440');
                              COMMIT;
                              END;

                              Changes I have done, for addr, instead of double quote, i used two single quotes and i put semicolon at the end of dbms_refresh statement.

                              Thanks to everybody who helped me and having patience in reading my issues.
                              Really thanks a lot.
                              • 12. Re: Error while running job which is in DBA_JOBS
                                Hoek
                                Lose the double quote and use 2 single quotes instead.
                                And bear in mind:
                                - next_date = DATE
                                - interval = VARCHAR2
                                /*
                                PROCEDURE SUBMIT
                                 Argumentnaam                   Type                    In/Out Standaard?
                                 ------------------------------ ----------------------- ------ --------
                                 JOB                            BINARY_INTEGER          OUT
                                 WHAT                           VARCHAR2                IN
                                 NEXT_DATE                      DATE                    IN     DEFAULT
                                 INTERVAL                       VARCHAR2                IN     DEFAULT
                                 NO_PARSE                       BOOLEAN                 IN     DEFAULT
                                 INSTANCE                       BINARY_INTEGER          IN     DEFAULT
                                 FORCE                          BOOLEAN                 IN     DEFAULT
                                */
                                
                                So, try:
                                
                                begin
                                dbms_job.submit( job => :jobno
                                               , what => 'begin dbms_refresh.refresh(''ADDR''); end;'
                                               , next_date => sysdate
                                               , interval => 'sysdate + 1/1440'
                                               );
                                commit;
                                end;
                                • 13. Re: Error while running job which is in DBA_JOBS
                                  665881
                                  Thanks Hoek,

                                  It worked. There is one another job, I need to create on another schema. But it didnot work.

                                  begin
                                  dbms_job.submit( job => :jobno
                                  , what => 'begin dbms_refresh.refresh(''bte''.''loc''); end;'
                                  , next_date => sysdate
                                  , interval => 'sysdate + 1/1440'
                                  );
                                  commit;
                                  end;

                                  Am I making any other mistake?
                                  Also, Is it commit mandatory here?
                                  • 14. Re: Error while running job which is in DBA_JOBS
                                    Hoek
                                    Am I making any other mistake?
                                    Try:
                                    begin
                                    dbms_job.submit( job => :jobno
                                    , what => 'begin dbms_refresh.refresh(''BTE.LOC''); end;'
                                    , next_date => sysdate
                                    , interval => 'sysdate + 1/1440'
                                    );
                                    commit;
                                    end;
                                    Also, Is it commit mandatory here?
                                    Yes, it sure is.

                                    Edited by: hoek on Aug 10, 2009 9:38 PM object names in uppercase

                                    Check http://tahiti.oracle.com and http://asktom.oracle com
                                    Do some searches over there, and read/learn a bit.
                                    You'll like it ;)

                                    Edited by: hoek on Aug 10, 2009 9:39 PM
                                    1 2 Previous Next