John,
I think the easiest way to do this is to create a stored procedure that
calls dbms_stats for you. you could then simply run your stored procedure
from dbms_jobs.
create or replace procedure run_stats is
begin
dbms_stats.gather_schema_stats(ownname=>'RPT_3G_MASTER',estimate_percent=>10
,cascade=>true);
end;
and then:
declare
l_job number;
begin
dbms_job.submit (l_job,'run_stats',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
make sense?
PS. I personally don't like DBMS_JOBS. I use either Cron, or Tivoli on NT
systems to schedule all jobs.
hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Tuesday, June 10, 2003 10:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname=>'RPT_3G_MASTER',estimate_percent=>10
,cascade=>true);
but trying to get that into an dbms_job is destroying my brain.The syntax I
am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE>
=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally I
think I want 2 single quotes around the cascade but I cannot get it to work
properly. The above example submits the job as
Job What
==========
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE>=TRUE');
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on his
web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
