DBMS_JOBS DOES NOT WORK - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: DBMS_JOBS DOES NOT WORK

  1. #11
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    lest we forget the user should be using dbms_stats if available on their particular db version

    steve
    I'm stmontgo and I approve of this message

  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by mike2000
    Pando,
    thanks for your prompt reply.

    I did grant the users who owned this job with EXECUTE to SYS.DBMS_UTILITY already so that when I run it manually it work.

    when you mean I have to direct grant to execute on DBMS_UTILITY, you mean I have to log in as SYS and run this query again:
    GRANT EXECUTE ON SYS.DBMS_UTILITY TO TEST.

    is that what you mean???

    thanks Pando

    yes, privs through roles wont work for pl/sql stored procedures

    also as someone suggested you should be using dbms_stats if you are using 8.1.x upwards

  3. #13
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    I am not sure.. I am just asking.. Does this have anything to do with execution time and interval??? Say like, execution time for this particular analyze is 2 mins and interval is 90 secs. Does this cause the job to be terminated?? Juts a doubt..
    -nagarjuna

  4. #14
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    no it wont

  5. #15
    Join Date
    Sep 2002
    Posts
    411
    thanks all for all quick response.

    I tried to search in here for DBMS_STAT. but I didn't find anything that was usefull.

    Could you please give me the instruction of how to use SYS.DBMS_STAT ???

    thanks

  6. #16
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    dbms_stats

    http://tahiti.oracle.com
    ..supplied pl/sql packages guide and of course
    you can desc dbms_stats to see what procedures and functions are available in that package.

    regards

    steve
    I'm stmontgo and I approve of this message

  7. #17
    Join Date
    Sep 2002
    Posts
    411
    Sorry to come back to this topic again.

    I know I can use dbms_stat but I really like to make the dbms_job work as well but for some reason DBMS_JOB still have problem.


    Below is the script of the user who own the package, but it kept saying unsuffiecient privilege on DBMS_UTILITY in the log file.

    I don't get this


    CREATE USER DB_ADMIN IDENTIFIED BY VALUES '910A6DB7DA7AC6FB'
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    PROFILE DEFAULT
    ACCOUNT UNLOCK
    /
    GRANT CONNECT TO DB_ADMIN WITH ADMIN OPTION
    /
    GRANT DBA TO DB_ADMIN WITH ADMIN OPTION
    /
    GRANT RESOURCE TO DB_ADMIN WITH ADMIN OPTION
    /
    ALTER USER DB_ADMIN DEFAULT ROLE CONNECT,
    DBA,
    RESOURCE
    /
    GRANT UNLIMITED TABLESPACE TO DB_ADMIN WITH ADMIN OPTION
    /
    GRANT EXECUTE ON SYS.DBMS_JOB TO DB_ADMIN
    /
    GRANT EXECUTE ON SYS.DBMS_UTILITY TO DB_ADMIN
    /



  8. #18
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Connecting as DB_ADMIN and isue the following:

    BEGIN
    DBMS_UTILITY.ANALYZE_SCHEMA('TEST','COMPUTE');
    END;
    /

    Does it raise any errors? Like "ORA-20000: Insufficient privileges..."? If so, you'll need to grant ANALYZE ANY system privilege to your user DB_ADMIN.

    If this runs succesfully in an anonymous block there is no reason why it shouldn't run inside a job.

    [Edited by jmodic on 10-08-2002 at 03:44 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #19
    Join Date
    Sep 2002
    Posts
    411
    var j number
    begin
    dbms_job.submit
    (
    :j,
    'SYS.DBMS_UTILITY.ANALYZE_SCHEMA(''TEST'',''COMPUTE'');
    ',sysdate,'sysdate+(90/(24*60*60))'
    );
    COMMIT;
    end;



  10. #20
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Mike, sorry for the confusion. After asking the question to which you just answered, I realised you've specified your submit command in your initial post - that's why I have rewritten my question, but while I was doing that, you have allready answered to the question that is not visible any more. Oh, oh, oh, this must be confusing to anyone except you and me, I guess .

    Anyway, can you run your WHAT parameter inside annonymous block as user DB_ADMIN?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width