RE: dbms_stats via dbms_job - syntax question
Thanks for the try Igor but that doesn't work either ERROR at line 1: ORA-06550: line 2, column 24: PLS-00201: identifier 'CASCADE' must be declared ORA-06550: line 1, column 99: Wolfgang's suggestion doesn't work and I have tried every permutation of quotes (including none at all) around the cacscade keyword as per Raj' suggestion with still no success. Thomas's suggestion of using a procedure was my failback position and is probably the way I will have to go. I am still frustrated that I cannot get the syntax to work correctly. Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it fails when I try a dbms_job.run Thanks to everyone anyway. John -Original Message- Sent: 10 June 2003 16:55 To: Multiple recipients of list ORACLE-L This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing parameters to stored procedure, you can not mix and match positional method with naming: either you use formal parameters, or not. It seems like you need naming. I used CHR(39) to get quotes around schema name. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Tuesday, June 10, 2003 9: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,''CASC ADE=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: Igor Neyman 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: 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).
RE: dbms_stats via dbms_job - syntax question SOLVED
Thanks for all the help. The problem was not with my coding but the syntax. In my worked example I had CASCADE=TRUE whereas it should have been CASCADE=TRUE. A subtle difference but one that caused me (and fellow listers) no end of hassle. So all the answers that I said were incorrect were not, they had all replicated my original typo. But one thing I proved was that you can mix and match positional and naming parameters The final working version for anyone who is interested is 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; / Thanks to all John -Original Message- Sent: 11 June 2003 09:24 To: Multiple recipients of list ORACLE-L Thanks for the try Igor but that doesn't work either ERROR at line 1: ORA-06550: line 2, column 24: PLS-00201: identifier 'CASCADE' must be declared ORA-06550: line 1, column 99: Wolfgang's suggestion doesn't work and I have tried every permutation of quotes (including none at all) around the cacscade keyword as per Raj' suggestion with still no success. Thomas's suggestion of using a procedure was my failback position and is probably the way I will have to go. I am still frustrated that I cannot get the syntax to work correctly. Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it fails when I try a dbms_job.run Thanks to everyone anyway. John -Original Message- Sent: 10 June 2003 16:55 To: Multiple recipients of list ORACLE-L This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing parameters to stored procedure, you can not mix and match positional method with naming: either you use formal parameters, or not. It seems like you need naming. I used CHR(39) to get quotes around schema name. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Tuesday, June 10, 2003 9: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,''CASC ADE=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: Igor Neyman 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: 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
RE: dbms_stats via dbms_job - syntax question
John, My code doesn't work probably because CASCADE = TRUE should actually be: CASCADE = TRUE. So, this should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Wednesday, June 11, 2003 3:24 AM To: Multiple recipients of list ORACLE-L Thanks for the try Igor but that doesn't work either ERROR at line 1: ORA-06550: line 2, column 24: PLS-00201: identifier 'CASCADE' must be declared ORA-06550: line 1, column 99: Wolfgang's suggestion doesn't work and I have tried every permutation of quotes (including none at all) around the cacscade keyword as per Raj' suggestion with still no success. Thomas's suggestion of using a procedure was my failback position and is probably the way I will have to go. I am still frustrated that I cannot get the syntax to work correctly. Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it fails when I try a dbms_job.run Thanks to everyone anyway. John -Original Message- Sent: 10 June 2003 16:55 To: Multiple recipients of list ORACLE-L This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing parameters to stored procedure, you can not mix and match positional method with naming: either you use formal parameters, or not. It seems like you need naming. I used CHR(39) to get quotes around schema name. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Tuesday, June 10, 2003 9: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,''CASC ADE=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: Igor Neyman 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: 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
RE: dbms_stats via dbms_job - syntax question SOLVED
But one thing I proved was that you can mix and match positional and naming parameters Hmm, I'd still rather use one OR another: you never know if mixing them would work in the next version... Why trying your luck?-:) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Wednesday, June 11, 2003 4:35 AM To: Multiple recipients of list ORACLE-L Thanks for all the help. The problem was not with my coding but the syntax. In my worked example I had CASCADE=TRUE whereas it should have been CASCADE=TRUE. A subtle difference but one that caused me (and fellow listers) no end of hassle. So all the answers that I said were incorrect were not, they had all replicated my original typo. But one thing I proved was that you can mix and match positional and naming parameters The final working version for anyone who is interested is declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCAD E=TRUE);', trunc(sysdate+1)+01/24,'sysdate+7'); end; / Thanks to all John -Original Message- Sent: 11 June 2003 09:24 To: Multiple recipients of list ORACLE-L Thanks for the try Igor but that doesn't work either ERROR at line 1: ORA-06550: line 2, column 24: PLS-00201: identifier 'CASCADE' must be declared ORA-06550: line 1, column 99: Wolfgang's suggestion doesn't work and I have tried every permutation of quotes (including none at all) around the cacscade keyword as per Raj' suggestion with still no success. Thomas's suggestion of using a procedure was my failback position and is probably the way I will have to go. I am still frustrated that I cannot get the syntax to work correctly. Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it fails when I try a dbms_job.run Thanks to everyone anyway. John -Original Message- Sent: 10 June 2003 16:55 To: Multiple recipients of list ORACLE-L This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing parameters to stored procedure, you can not mix and match positional method with naming: either you use formal parameters, or not. It seems like you need naming. I used CHR(39) to get quotes around schema name. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Tuesday, June 10, 2003 9: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,''CASC ADE=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: Igor Neyman 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
RE: dbms_stats via dbms_job - syntax question SOLVED
John Then be sure to set the init.ora parameter _do_what_I_mean=true It solved most of my problems. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 11, 2003 4:35 AM To: Multiple recipients of list ORACLE-L Thanks for all the help. The problem was not with my coding but the syntax. In my worked example I had CASCADE=TRUE whereas it should have been CASCADE=TRUE. A subtle difference but one that caused me (and fellow listers) no end of hassle. So all the answers that I said were incorrect were not, they had all replicated my original typo. But one thing I proved was that you can mix and match positional and naming parameters The final working version for anyone who is interested is declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCADE=T RUE);', trunc(sysdate+1)+01/24,'sysdate+7'); end; / Thanks to all John -Original Message- Sent: 11 June 2003 09:24 To: Multiple recipients of list ORACLE-L Thanks for the try Igor but that doesn't work either ERROR at line 1: ORA-06550: line 2, column 24: PLS-00201: identifier 'CASCADE' must be declared ORA-06550: line 1, column 99: Wolfgang's suggestion doesn't work and I have tried every permutation of quotes (including none at all) around the cacscade keyword as per Raj' suggestion with still no success. Thomas's suggestion of using a procedure was my failback position and is probably the way I will have to go. I am still frustrated that I cannot get the syntax to work correctly. Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it fails when I try a dbms_job.run Thanks to everyone anyway. John -Original Message- Sent: 10 June 2003 16:55 To: Multiple recipients of list ORACLE-L This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing parameters to stored procedure, you can not mix and match positional method with naming: either you use formal parameters, or not. It seems like you need naming. I used CHR(39) to get quotes around schema name. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Tuesday, June 10, 2003 9: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,''CASC ADE=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: Igor Neyman 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
RE: dbms_stats via dbms_job - syntax question
Title: RE: dbms_stats via dbms_job - syntax question you don't need quotes around cascade=true ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Hallas, John, Tech Dev [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 10, 2003 10:35 AM To: Multiple recipients of list ORACLE-L Subject: dbms_stats via dbms_job - syntax question 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 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: dbms_stats via dbms_job - syntax question
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).
RE: dbms_stats via dbms_job - syntax question
This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing parameters to stored procedure, you can not mix and match positional method with naming: either you use formal parameters, or not. It seems like you need naming. I used CHR(39) to get quotes around schema name. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Tuesday, June 10, 2003 9: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,''CASC ADE=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: Igor Neyman 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).
Re: dbms_stats via dbms_job - syntax question
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).