dbms_submit to run dbms_stats failed
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: dbms_submit to run dbms_stats failed

  1. #1
    Join Date
    Oct 2003
    Posts
    312

    dbms_submit to run dbms_stats failed

    Hi all,

    I am trying to schedule a job to run dbms_stats and it failed, below is my syntax:

    var j number
    begin
    dbms_job.submit
    (
    :j,
    'SYS.DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_A',CASCADE=>TRUE);',
    'SYS.DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_B',CASCADE=>TRUE);',
    'SYS.DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_C',CASCADE=>TRUE);',
    'SYS.DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_D',CASCADE=>TRUE);',
    SYSDATE,
    'NEXT_DAY(trunc(sysdate),''SATURDAY'')+1/12'
    );
    COMMIT;
    end;



    any advises???? thanks

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    sure, post the error message.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    AFAIK, you can't submit more than one proc. So either submit several jobs or put the calls to DBMS_STATS into a single proc.

  4. #4
    Join Date
    Oct 2003
    Posts
    312
    since I have more than schemas in the datbase, instead of scheduling many job, can I use DBMS_STATS.GATHER_DATABASE_STATS????

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    There might be the problem that DBMS_STATS.GATHER_DATABASE_STATS collects stats on the SYS schema - which might not be a good idea. Someone raised the question here and even Jurij didn't know the answer.

  6. #6
    Join Date
    Oct 2003
    Posts
    312
    Dapi,

    I also saw that somewhere saying dbms_stats.gather_database_stats would collect stats on SYS which is not a good thing to do. couple questions:

    1. if dbms_stats.gather_database_stats collect stats on SYS, so when can you use it???? for what purpose???

    2. if I have close to 20 schemas in my database and I want to collect stats for all of them once a week, so I have to submit the job via dbms_submit 20 times????

    thanks so much for your input

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Originally posted by learning_bee
    Dapi,

    I also saw that somewhere saying dbms_stats.gather_database_stats would collect stats on SYS which is not a good thing to do. couple questions:

    1. if dbms_stats.gather_database_stats collect stats on SYS, so when can you use it???? for what purpose???

    2. if I have close to 20 schemas in my database and I want to collect stats for all of them once a week, so I have to submit the job via dbms_submit 20 times????

    thanks so much for your input
    no.

    Use a procecure to wrap them together as dapi suggested

    Code:
    create or replace procedure gather_stats as
    begin
    dbms_stats.gather_schema_stats(...);
    dbms_stats.gather_schema_stats(...);
    dbms_stats.gather_schema_stats(...);
    dbms_stats.gather_schema_stats(...);
    end gather_stats;
    /
    
    declare
    var j number
    begin
    dbms_job.submit
    (
    :j,
    sys.gather_stats,
    SYSDATE,
    'NEXT_DAY(trunc(sysdate),''SATURDAY'')+1/12'
    );
    COMMIT;
    end;
    /
    as for system stats, sometimes they can be useful - other times not

  8. #8
    Join Date
    Oct 2003
    Posts
    312
    Originally posted by davey23uk
    no.

    as for system stats, sometimes they can be useful - other times not

    sorry to come back to this question again, when could be good to run system stats????

  9. #9
    Join Date
    Oct 2003
    Posts
    312
    thanks Davey,

    when I run the below proc,

    create or replace procedure gather_stats as
    begin
    dbms_stats.gather_schema_stats(...);
    dbms_stats.gather_schema_stats(...);
    dbms_stats.gather_schema_stats(...);
    dbms_stats.gather_schema_stats(...);
    end gather_stats;
    /


    I got an error about "Error: ORA-20000: Insufficient privileges to analyze an object in Schema
    ORA-06512: at line 1, Batch 1 Line 1 Col 1"

    does the user who own this proc has to have SYSDBA privilege??? what privilege do I need to run this proc???

    thanks,

  10. #10
    Join Date
    Oct 2003
    Posts
    312
    I got it.

    grant analyze any and grant select any table to the user.

    thanks all

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