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

Thread: DBMS_JOBS DOES NOT WORK

  1. #21
    Join Date
    Sep 2002
    Posts
    411
    Jmodic,

    n/p

    what do you mean when you are saying the WHAT parameter, could you please be more clear on this and I will run it.

    Thanks

  2. #22
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    DBMS_JOB.SUBMIT has bunch of output/input parameters - one of the input parameters is WHAT, which defines what exactly the job has to do. In your case, WHAT parameter is 'DBMS_UTILITY.ANALYZE_SCHEMA(''TEST'',''COMPUTE'');'.

    So, can you succesfully run

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

    as DB_ADMIN user?

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #23
    Join Date
    Sep 2002
    Posts
    411
    Jmodic,

    When I ran

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

    it ran fine, but when I sued DBMS_JOB and it won't run , I look into the log files and it said insufficient privilege to run DBMS_UTILITY, but look at the user from DDL, I have the grant privilege on DB_ADMIN.


  4. #24
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Can you send error stack (I mean exact ORA error numbers and text) from your log?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #25
    Join Date
    Sep 2002
    Posts
    411
    Jmodic,

    Below is what I have on the errrors:

    Redo thread mounted by this instance: 1

    Oracle process number: 15

    Windows thread id: 24664, image: ORACLE.EXE


    *** SESSION ID:(14.9679) 2002-10-09 08:52:22.312
    *** 2002-10-09 08:52:22.312
    ORA-12012: error on auto execute of job 25
    ORA-20000: You have insufficient privileges for an object in this schema.
    ORA-06512: at "SYS.DBMS_UTILITY", line 335
    ORA-06512: at line 1

  6. #26
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by mike2000
    ORA-20000: You have insufficient privileges for an object in this schema.
    That's exactly the error I was asking you about in one of my previous posts in this thread! This error (ORA-20000) is not oracle "generic" error message, it is user defined message, raised by the user defined exception. This errror message is defined inside DBMS_UTILITY.ANALYZE_SCHEMA procedure.

    What this realy means is that you have all the needed privileges to run DBMS_JOB and DBMS_UTILITY, but you don't have enough privileges to analyze all the tables in that schema through PL/SQL API. You need to do the following:

    GRANT ANALYZE ALL TO db_admin;

    and you'll be fine.

    BTW, I doubt taht your user DB_ADMIN can successuly run the PL/SQL block I was asking you to run:

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

    It should raise the same ORA-20000 error.
    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