-
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
-
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
-
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
-
-
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
-
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
-
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
/
-
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?
-
var j number
begin
dbms_job.submit
(
:j,
'SYS.DBMS_UTILITY.ANALYZE_SCHEMA(''TEST'',''COMPUTE'');
',sysdate,'sysdate+(90/(24*60*60))'
);
COMMIT;
end;
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|