Re: dbms_stats via dbms_job

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 10 Jun 2003 08:32:25 -0700
Message-ID: <F001.005AE494.20030610075441@fatcity.com>

Why not submit it exactly the same as you do it interactively:

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).

Received on Tue Jun 10 2003 - 10:32:25 CDT

Re: dbms_stats via dbms_job - syntax question