declare
l_job number;
begin
dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(ownname=>''RPT_3G_MASTER'',estimate_percent=>10,cascade=>true);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
All you need to do is replace the single quotes around the schema name with double quotes.
At 06:34 AM 6/10/2003 -0800, you wrote:
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
Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).