RE: dbms_job issue.
Have you checked number of queues lately? Kind Regards, Hatzistavrou Yannis Database Administrator SchlumbergerSema Phone ext. 478 Email: [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 8:30 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME -- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER: This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
RE: dbms_job issue.
Re-submit the jobthat should work It has a long theory!! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:00 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME -- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER: This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
RE: dbms_job issue.
You said for the last 3 days, so what do you do to get it running again? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 12:30 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME -- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER: This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
RE: dbms_job issue.
Already tried to resubmit it twice, everytime its breaking around mid night. -Original Message-From: Venu Gopal [mailto:[EMAIL PROTECTED]Sent: Thursday, September 18, 2003 10:40 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job issue. Re-submit the jobthat should work It has a long theory!! -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]Sent: Thursday, September 18, 2003 11:00 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME-- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *** DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
RE: dbms_job issue.
IIRC it is a bug, on 5L oracle sometimes _forgets_ to run the job. We noticed this in early versions of 9i. We stopped using it and we use cron instead. If you want to test this, set job_queue_processes to say 20 and see, it will probably work. The workaround from oracle states to set it to 'really high' number. I don't remember the bug# right now ... -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Thursday, September 18, 2003 2:00 PMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job issue. Already tried to resubmit it twice, everytime its breaking around mid night. -Original Message-From: Venu Gopal [mailto:[EMAIL PROTECTED]Sent: Thursday, September 18, 2003 10:40 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job issue. Re-submit the job...that should work... It has a long theory...!! -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]Sent: Thursday, September 18, 2003 11:00 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME-- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *** DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. 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.*1
RE: dbms_job issue.
Resubmit it and it runs fine until mid night. So I really dont have any stats for this week. -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]Sent: Thursday, September 18, 2003 10:45 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job issue. You said for the last 3 days, so what do you do to get it running again? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]Sent: Thursday, September 18, 2003 12:30 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME-- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
Re: dbms_job issue.
[EMAIL PROTECTED] wrote: Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I am not seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME -- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0175 The big nuisance with jobs is that they leave you in the dark when they fail. You may find some of the stuff here interesting to diagnose : http://www.oriole.com/aunt_2001_0.html (March letter) -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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_job
It would seem that dbms_system has become undocumented. It used to be partially documented, e.g. there was a description of set_sql_trace_in_session, but they seem to have pulled that as of 8.1. You're supposed to use dbms_support now, but that too seems to be undocumented. At least on Solaris, you have to beg support for the 8i install script, but the 9i install script is included in $ORACLE_HOME/rdbms/admin. There are some notes about this in some of the forums at metalink ( I searched 'dbms_system dbms_support'). HTH, -Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job Hello list, I am running 9.2.0.1.0 enterprise edition on win32. Why doesn't the Supplied PLSQL Packages and Types Reference docs mention dbms_system ? It doesn't seem to be explained anywhere in the docs although the admin docs say that it can be used enable sql_trace for another session. I was able to do that only after learning about its parameters thru : sql describe dbms_system while I was logged in as sysdba. .. LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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_job
Hi The package dbms_system is now private, the body and header are both created in $ORACLE_HOME/rdbms/admin/prvtutil.plb. The sql file $ORACLE_HOME/rdbms/admin/dbmsutil.sql has the following in it now: Rem dbms_system - database system level commands (moved to Rem prvtutil.sql for more obscurity) kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan 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_job
Title: RE: dbms_job www.orafaq.com/papers/dbms_sys.doc 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: Pete Finnigan [mailto:[EMAIL PROTECTED]] Sent: Friday, July 25, 2003 5:14 PM To: Multiple recipients of list ORACLE-L Subject: Re: dbms_job Hi The package dbms_system is now private, the body and header are both created in $ORACLE_HOME/rdbms/admin/prvtutil.plb. The sql file $ORACLE_HOME/rdbms/admin/dbmsutil.sql has the following in it now: Rem dbms_system - database system level commands (moved to Rem prvtutil.sql for more obscurity) kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan 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). 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_job
Thanks pete - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, July 26, 2003 02:44 Hi The package dbms_system is now private, the body and header are both created in $ORACLE_HOME/rdbms/admin/prvtutil.plb. The sql file $ORACLE_HOME/rdbms/admin/dbmsutil.sql has the following in it now: Rem dbms_system - database system level commands (moved to Rem prvtutil.sql for more obscurity) kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan 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: [EMAIL PROTECTED] 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_job
Thanks chris. Let me know if you find out anything more. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, July 25, 2003 23:39 It would seem that dbms_system has become undocumented. It used to be partially documented, e.g. there was a description of set_sql_trace_in_session, but they seem to have pulled that as of 8.1. You're supposed to use dbms_support now, but that too seems to be undocumented. At least on Solaris, you have to beg support for the 8i install script, but the 9i install script is included in $ORACLE_HOME/rdbms/admin. There are some notes about this in some of the forums at metalink ( I searched 'dbms_system dbms_support'). HTH, -Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job Hello list, I am running 9.2.0.1.0 enterprise edition on win32. Why doesn't the Supplied PLSQL Packages and Types Reference docs mention dbms_system ? It doesn't seem to be explained anywhere in the docs although the admin docs say that it can be used enable sql_trace for another session. I was able to do that only after learning about its parameters thru : sql describe dbms_system while I was logged in as sysdba. .. LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: [EMAIL PROTECTED] 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_JOB scheduling
Title: RE: DBMS_JOB scheduling Garry, 1. have you tried select to_char(sysdate,'D') from dual ?? This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number. nevertheless, I think what you have attempted is fantastic and worthy of adoption ... 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: Garry Gillies [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 23, 2003 5:59 AM To: Multiple recipients of list ORACLE-L Subject: DBMS_JOB scheduling Any Interest? The DBMS_JOB package is supplied by Oracle to allow the running of procedures at regular intervals. Unfortunately the INTERVAL parameter is limited to 128 characters, which prevents you from getting very complex (user defined functions [in the interval parameter] do not work well - according to Fuerstein in his book Oracle Built In Packages). The situation is eased somewhat by the fact that the NEXT_DATE parameter can be supplied to the procedure as an in/out parameter - and the procedure can contain whatever code is necessary to calculate when next to run. This is all very well, but custom coding scheduling routines can quickly become tedious. On the basis of do it once and get it over with I have written a function called NEXT_DATE which I have wrapped in a package called CRON. There is a Unix program called cron which runs jobs on a regular basis. Although the scheduling data supplied to cron is simple and concise, complex schedules are easy to specify. The NEXT_DATE function takes in a cron schedule string and returns the next date that conforms to the schedule - or you can supply a cron schedule and a date and it will return the first date after the supplied date that conforms to the schedule. At the moment it is not very friendly on the error detection front. A VALUE_ERROR is returned if it deems the cron schedule to be invalid. You will also get a VALUE_ERROR if the next valid date is more than twenty seven years in the future. DBMS_OUTPUT is used to display error messages which will hopefully give you a clue. This will be improved if I receive enough complaints ( and suggestions for improvements). THE CRON SCHEDULE A cron schedule consists of five components, each separated from the next by a space. The syntax is identical for all components. The components represent Minute in Hour Hour in day Day in month Month in year Day of Week - A bit of a bugger this one. In Unix land the day numbering runs from 0-6 with 0 being Sunday. In Oracle the day numbering depends on the setting of NLS_TERRITORY. I have chosen to go with ISO standard 8601:1998 which runs from 1-7 with 1 being Monday. This is so close to the Unix convention that I can interpret Unix cron schedules correctly. Curiously, Oracle do not provide a date format which supplies this number. The ISO week number is available with the format 'IW', but not the ISO day number. If you have a field of type date called dt, you can obtain the ISO day number with ( trunc(dt) - trunc(dt ,'IW') ) + 1 A component can consist of an asterisk * which represents all valid values or a number of elements separated by a comma (if only one element is supplied, forget the comma). An element can be a single number - valid for the component (32 in Day in month is invalid) or two numbers separated by a hyphen - which represents a range. EXAMPLES Run every hour on the hour 0 * * * * Run twice every hour, on the hour and on the half hour 0,30 * * * * Run twice every hour, on the hour and on the half hour between 08:00 and 16:59 0,30 8-16 * * * Run twice every hour, on the hour and on the half hour between 08:00 and 16:59, Monday to Friday 0,30 8-16 * * 1-5 Run at 11:12 every Friday the 13th 11 12 13 * 5 Run at 04:00 every leap year on february 29 0 4 29 2 * Run at 04:00 every leap year on february 29 when february 29 is a Thursday 0 4 29 2 4 Garry Gillies Database Administrator Business Systems Weir Pumps Ltd 149 Newlands Road, Cathcart, Glasgow, G44 4EX T: +44 0141 308 3982 F: +44 0141 633 1147 E: [EMAIL PROTECTED] *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
RE: DBMS_JOB scheduling
Title: RE: DBMS_JOB scheduling Raj, You must be speaking from UNIX heights -J Under Windows I find dbms_job much more reliable than windows at scheduling. Actually, never had problems with dbms_job forgetting to run a job. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, Rajendra Sent: Wednesday, July 23, 2003 9:24 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB scheduling Garry, 1. have you tried select to_char(sysdate,'D') from dual ?? This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number. nevertheless, I think what you have attempted is fantastic and worthy of adoption ... 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: Garry Gillies [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 23, 2003 5:59 AM To: Multiple recipients of list ORACLE-L Subject: DBMS_JOB scheduling Any Interest? The DBMS_JOB package is supplied by Oracle to allow the running of procedures at regular intervals. Unfortunately the INTERVAL parameter is limited to 128 characters, which prevents you from getting very complex (user defined functions [in the interval parameter] do not work well - according to Fuerstein in his book Oracle Built In Packages). The situation is eased somewhat by the fact that the NEXT_DATE parameter can be supplied to the procedure as an in/out parameter - and the procedure can contain whatever code is necessary to calculate when next to run. This is all very well, but custom coding scheduling routines can quickly become tedious. On the basis of do it once and get it over with I have written a function called NEXT_DATE which I have wrapped in a package called CRON. There is a Unix program called cron which runs jobs on a regular basis. Although the scheduling data supplied to cron is simple and concise, complex schedules are easy to specify. The NEXT_DATE function takes in a cron schedule string and returns the next date that conforms to the schedule - or you can supply a cron schedule and a date and it will return the first date after the supplied date that conforms to the schedule. At the moment it is not very friendly on the error detection front. A VALUE_ERROR is returned if it deems the cron schedule to be invalid. You will also get a VALUE_ERROR if the next valid date is more than twenty seven years in the future. DBMS_OUTPUT is used to display error messages which will hopefully give you a clue. This will be improved if I receive enough complaints ( and suggestions for improvements). THE CRON SCHEDULE A cron schedule consists of five components, each separated from the next by a space. The syntax is identical for all components. The components represent Minute in Hour Hour in day Day in month Month in year Day of Week - A bit of a bugger this one. In Unix land the day numbering runs from 0-6 with 0 being Sunday. In Oracle the day numbering depends on the setting of NLS_TERRITORY. I have chosen to go with ISO standard 8601:1998 which runs from 1-7 with 1 being Monday. This is so close to the Unix convention that I can interpret Unix cron schedules correctly. Curiously, Oracle do not provide a date format which supplies this number. The ISO week number is available with the format 'IW', but not the ISO day number. If you have a field of type date called dt, you can obtain the ISO day number with ( trunc(dt) - trunc(dt ,'IW') ) + 1 A component can consist of an asterisk * which represents all valid values or a number of elements separated by a comma (if only one element is supplied, forget the comma). An element can be a single number - valid for the component (32 in Day in month is invalid) or two numbers separated by a hyphen - which represents a range. EXAMPLES Run every hour on the hour 0 * * * * Run twice every hour, on the hour and on the half hour 0,30 * * * * Run twice every hour, on the hour and on the half hour between 08:00 and 16:59 0,30 8-16 * * * Run twice every hour, on the hour and on the half hour between 08:00 and 16:59, Monday to Friday 0,30 8-16 * * 1-5 Run at 11:12 every Friday the 13th 11 12 13 * 5 Run at 04:00 every leap year on february 29 0 4 29 2 * Run at 04:00 every leap year on february 29 when february 29 is a Thursday 0 4 29 2 4 Garry Gillies Database Administrator Business Systems Weir Pumps Ltd 149 Newlands Road, Cathcart, Glasgow, G44 4EX T: +44 0141 308 3982 F: +44 0141 633 1147 E: [EMAIL
RE: DBMS_JOB scheduling
1. have you tried select to_char(sysdate,'D') from dual ?? Yes, and it works fine on MY servers, but the docs say that the value returned depends on your NLS_TERRITORY setting. I am trying to get it to work for everybody. This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number. I am sorry to hear that. We are on 8.1.7 and have been using since 7.3 with no major problems. nevertheless, I think what you have attempted is fantastic and worthy of adoption ... Are you looking for money? :-) Garry Gillies Database Administrator Business Systems Weir Pumps Ltd 149 Newlands Road, Cathcart, Glasgow, G44 4EX T: +44 0141 308 3982 F: +44 0141 633 1147 E: [EMAIL PROTECTED] Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 23/07/03 15:24 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: DBMS_JOB scheduling Garry, 1. have you tried select to_char(sysdate,'D') from dual ?? This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number. nevertheless, I think what you have attempted is fantastic and worthy of adoption ... CONFIDENTIAL: The information contained in this email (including any attachments) is confidential, subject to copyright and for the use of the intended recipient only. If you are not the intended recipient please delete this message after notifying the sender. Unauthorised retention, alteration or distribution of this email is forbidden and may be actionable. Attachments are opened at your own risk and you are advised to scan incoming email for viruses before opening any attached files. We give no guarantee that any communication is virus-free and accept no responsibility for virus contamination or other system loss or damage of any kind. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Garry Gillies 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_JOB scheduling
Title: RE: DBMS_JOB scheduling Igor, you are right ... as a unwritten policy we don't allow windows databases ... even for crash test dummies ... 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: Igor Neyman [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 23, 2003 10:40 AMTo: Multiple recipients of list ORACLE-LSubject: RE: DBMS_JOB scheduling Raj, You must be speaking from "UNIX heights" -J Under Windows I find dbms_job much more reliable than windows "at" scheduling. Actually, never had problems with dbms_job "forgetting" to run a job. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Wednesday, July 23, 2003 9:24 AMTo: Multiple recipients of list ORACLE-LSubject: RE: DBMS_JOB scheduling Garry, 1. have you tried select to_char(sysdate,'D') from dual ?? This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number. nevertheless, I think what you have attempted is fantastic and worthy of adoption ... 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: Garry Gillies [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 23, 2003 5:59 AM To: Multiple recipients of list ORACLE-L Subject: DBMS_JOB scheduling Any Interest? The DBMS_JOB package is supplied by Oracle to allow the running of procedures at regular intervals. Unfortunately the INTERVAL parameter is limited to 128 characters, which prevents you from getting very complex (user defined functions [in the interval parameter] do not work well - according to Fuerstein in his book Oracle Built In Packages). The situation is eased somewhat by the fact that the NEXT_DATE parameter can be supplied to the procedure as an in/out parameter - and the procedure can contain whatever code is necessary to calculate when next to run. This is all very well, but custom coding scheduling routines can quickly become tedious. On the basis of "do it once and get it over with" I have written a function called NEXT_DATE which I have wrapped in a package called CRON. There is a Unix program called cron which runs jobs on a regular basis. Although the scheduling data supplied to cron is simple and concise, complex schedules are easy to specify. The NEXT_DATE function takes in a cron schedule string and returns the next date that conforms to the schedule - or you can supply a cron schedule and a date and it will return the first date after the supplied date that conforms to the schedule. At the moment it is not very friendly on the error detection front. A VALUE_ERROR is returned if it deems the cron schedule to be invalid. You will also get a VALUE_ERROR if the next valid date is more than twenty seven years in the future. DBMS_OUTPUT is used to display error messages which will hopefully give you a clue. This will be improved if I receive enough complaints ( and suggestions for improvements). THE CRON SCHEDULE A cron schedule consists of five components, each separated from the next by a space. The syntax is identical for all components. The components represent Minute in Hour Hour in day Day in month Month in year Day of Week - A bit of a bugger this one. In Unix land the day numbering runs from 0-6 with 0 being Sunday. In Oracle the day numbering depends on the setting of NLS_TERRITORY. I have chosen to go with ISO standard 8601:1998 which runs from 1-7 with 1 being Monday. This is so close to the Unix convention that I can interpret Unix cron schedules correctly. Curiously, Oracle do not provide a date format which supplies this number. The ISO week number is available with the format 'IW', but not the ISO day number. If you have a field of type date called dt, you can obtain the ISO day number with ( trunc(dt) - trunc(dt ,'IW') ) + 1 A component can consist of an asterisk * which represents all valid values or a number of elements separated by a comma (if only one element is supplied, forget the comma). An element can be a single number - valid for the co
RE: DBMS_JOB scheduling
Title: RE: DBMS_JOB scheduling nope ... at-least not yet. 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: Garry Gillies [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 23, 2003 11:14 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB scheduling Are you looking for money? :-) Garry Gillies 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_job
It will submit the job and continue. Even nicer is that if you subsequently do a rollback later, then the job will be roll'd back as well. In this way, you could put dbms_job into (say) a trigger and if the statement later roll's back you don't end up with a mess hth connor --- Basavaraja, Ravindra [EMAIL PROTECTED] wrote: Hi, I have a procedure that submits a dbms_job for immediate processing for onetime.I want to know if the procedure that submits the job will have to wait till the job gets executed to execute the next piece of code after the dbms_job.submit() in the procedure or will the procedure submit the dbms_job and continue executing the next line of code in the procedure. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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_JOB : Can submit but does not execute=working now
Hi,
Thanks to all who reply. After setting the job_queue_processes to 1 and
job_queue_interval = 90, the job runs successfully. Thank you.
Regds,
Catherine
-Original Message-
From: Jay Hostetter [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 19, 2003 8:25 PM
To: [EMAIL PROTECTED]; CHAN Chor Ling Catherine (CSC)
Subject:RE: DBMS_JOB : Can submit but does not execute
Make sure job_queue_processes 0. Also check job_queue_interval.
Jay Hostetter
Oracle DBA
D. E. Communications
Ephrata, PA USA
[EMAIL PROTECTED] 02/18/03 08:28PM
Hi Gurus,
I use the following command to execute immediately.
Connected.
SQL BEGIN
2 DBMS_JOB.RUN(1);
3 END;
4 /
PL/SQL procedure successfully completed.
--
-- I change the time for next execution to check whether it can submit
on 18 Feb 2003 at 8am
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
It did not execute. Any advice ? TIA
Regds,
Catherine
-Original Message-
From: CHAN Chor Ling Catherine (CSC)
Sent: Wednesday, February 19, 2003 9:15 AM
To: '[EMAIL PROTECTED]'
Subject:DBMS_JOB : Can submit but does not
execute
Hi Gurus,
I have submitted a job but it does not execute. Why?
Does the owner require any privileges ? However, I can use DBMS_JOB.CHANGE command to
execute immediately. I would like the job to execute on a daily basis. Any advice ?
TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4
DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- --
--
SCHEMA_USERLAST_DATE LAST_SEC
THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
-- -
- - -- -
INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV
INSTANCE
RE: DBMS_JOB : Can submit but does not execute
Title: RE: DBMS_JOB : Can submit but does not execute In 8i I have had bad luck of submitting it at sysdate and they don't fire ... Once I got burned, I can afford 5 seconds ... if it is that critical, dbms_job isn't the tool for that job anymore. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 19, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB : Can submit but does not execute That shouldn't matter. Look at DBA_JOBS when you startup an instance after it's been down for a while (not that it would ever happen!). All pending jobs (nextdate = SYSDATE) should be firing, at least as many job_queue_processes as you have in the init.ora. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Wednesday, February 19, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Also as a practice, I always submit sysdate + 5 seconds instead of sysdate. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- Sent: Wednesday, February 19, 2003 7:34 AM To: Multiple recipients of list ORACLE-L Make sure job_queue_processes 0. Also check job_queue_interval. Jay Hostetter Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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). *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.*1
RE: DBMS_JOB : Can submit but does not execute
Title: RE: DBMS_JOB : Can submit but does not execute Maybe this is stating the obvious, but make sure you do a COMMIT after submitting the job. - Dave -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Thursday, February 20, 2003 6:59 AMTo: Multiple recipients of list ORACLE-LSubject: RE: DBMS_JOB : Can submit but does not execute In 8i I have had bad luck of submitting it at sysdate and they don't fire ... Once I got burned, I can afford 5 seconds ... if it is that critical, dbms_job isn't the tool for that job anymore. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 19, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB : Can submit but does not execute That shouldn't matter. Look at DBA_JOBS when you startup an instance after it's been down for a while (not that it would ever happen!). All pending jobs (nextdate = SYSDATE) should be firing, at least as many job_queue_processes as you have in the init.ora. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Wednesday, February 19, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Also as a practice, I always submit sysdate + 5 seconds instead of sysdate. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- Sent: Wednesday, February 19, 2003 7:34 AM To: Multiple recipients of list ORACLE-L Make sure job_queue_processes 0. Also check job_queue_interval. Jay Hostetter Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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_JOB : Can submit but does not execute
You have to give commit after submit the job.
-Original Message-
Ling Catherine (CSC)
Sent: Tuesday, February 18, 2003 8:14 PM
To: Multiple recipients of list ORACLE-L
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner
require any privileges ? However, I can use DBMS_JOB.CHANGE command to
execute immediately. I would like the job to execute on a daily basis.
Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDA
TE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USERLAST_DATE LAST_SEC THIS_DATE THIS_SEC
NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - -
- -- - INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV
INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:20
18-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENCY='AMERICA' NLS_NUME RIC_CHARACTERS='.,'
NLS_DATE_FORMAT='DD-MON-YY' NLS_DATE_LANGUAGE='AMERICAN'
NLS_SORT='BINARY'
01020002
0
Regds,
Catherine
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: CHAN Chor Ling Catherine (CSC)
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: Ramon E. Estevez
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_JOB : Can submit but does not execute
Have you set the job_queue_processes parameter in you init.ora file?
CHAN Chor Ling Catherine (CSC) wrote:
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner require any privileges ? However, I can use DBMS_JOB.CHANGE command to execute immediately. I would like the job to execute on a daily basis. Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 ' PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USERLAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - - - -- -
INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:2018-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUME
RIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-YY' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
01020002 0
Regds,
Catherine
--
Ing. Michal Zaschke
DB Administrator
Sokolovska uhelna, a.s.
tel.: +420 352 465417
e-mail: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Michal Zaschke
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_JOB : Can submit but does not execute
You have to run the job after you submit it.
SQL exec dbms_job.run(24);
Also make sure all init.ora parameters are set eg (job_queue_interval ,
job_queue_processes )
HTH,
Rajesh
-Original Message-
Ling Catherine (CSC)
Sent: Tuesday, February 18, 2003 8:14 PM
To: Multiple recipients of list ORACLE-L
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner
require any privileges ? However, I can use DBMS_JOB.CHANGE command to
execute immediately. I would like the job to execute on a daily basis.
Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDA
TE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USERLAST_DATE LAST_SEC THIS_DATE THIS_SEC
NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - -
- -- - INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV
INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:20
18-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENCY='AMERICA' NLS_NUME RIC_CHARACTERS='.,'
NLS_DATE_FORMAT='DD-MON-YY' NLS_DATE_LANGUAGE='AMERICAN'
NLS_SORT='BINARY'
01020002
0
Regds,
Catherine
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: CHAN Chor Ling Catherine (CSC)
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: Ramon E. Estevez
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: Rajesh Dayal
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_JOB : Can submit but does not execute
Make sure job_queue_processes 0. Also check job_queue_interval.
Jay Hostetter
Oracle DBA
D. E. Communications
Ephrata, PA USA
[EMAIL PROTECTED] 02/18/03 08:28PM
Hi Gurus,
I use the following command to execute immediately.
Connected.
SQL BEGIN
2 DBMS_JOB.RUN(1);
3 END;
4 /
PL/SQL procedure successfully completed.
--
-- I change the time for next execution to check whether it can submit on 18 Feb 2003
at 8am
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 ' PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
It did not execute. Any advice ? TIA
Regds,
Catherine
-Original Message-
From: CHAN Chor Ling Catherine (CSC)
Sent: Wednesday, February 19, 2003 9:15 AM
To: '[EMAIL PROTECTED]'
Subject:DBMS_JOB : Can submit but does not execute
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner
require any privileges ? However, I can use DBMS_JOB.CHANGE command to execute
immediately. I would like the job to execute on a daily basis. Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USERLAST_DATE LAST_SEC THIS_DATE THIS_SEC
NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - -
- -- -
INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV
INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:20
18-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENCY='AMERICA' NLS_NUME
RIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-YY'
NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
01020002
0
Regds,
Catherine
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: CHAN Chor Ling Catherine (CSC)
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).
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received
RE: DBMS_JOB : Can submit but does not execute
Title: RE: DBMS_JOB : Can submit but does not execute
Also as a practice, I always submit sysdate + 5 seconds instead of sysdate.
Raj
-
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!
-Original Message-
From: Jay Hostetter [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 19, 2003 7:34 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: DBMS_JOB : Can submit but does not execute
Make sure job_queue_processes 0. Also check job_queue_interval.
Jay Hostetter
Oracle DBA
D. E. Communications
Ephrata, PA USA
[EMAIL PROTECTED] 02/18/03 08:28PM
Hi Gurus,
I use the following command to execute immediately.
Connected.
SQL BEGIN
2 DBMS_JOB.RUN(1);
3 END;
4 /
PL/SQL procedure successfully completed.
--
-- I change the time for next execution to check whether it can submit on 18 Feb 2003 at 8am
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 ' PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
It did not execute. Any advice ? TIA
Regds,
Catherine
-Original Message-
From: CHAN Chor Ling Catherine (CSC)
Sent: Wednesday, February 19, 2003 9:15 AM
To: '[EMAIL PROTECTED]'
Subject: DBMS_JOB : Can submit but does not execute
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner require any privileges ? However, I can use DBMS_JOB.CHANGE command to execute immediately. I would like the job to execute on a daily basis. Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2 job BINARY_INTEGER;
3 BEGIN
4 DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5 DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 ' PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - - - -- -
INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:20 18-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUME
RIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-YY' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
01020002 0
Regds,
Catherine
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: CHAN Chor Ling Catherine (CSC)
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).
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Hostetter
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
RE: DBMS_JOB : Can submit but does not execute
That shouldn't matter. Look at DBA_JOBS when you startup an instance after it's been down for a while (not that it would ever happen!). All pending jobs (nextdate = SYSDATE) should be firing, at least as many job_queue_processes as you have in the init.ora. Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Wednesday, February 19, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Also as a practice, I always submit sysdate + 5 seconds instead of sysdate. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- Sent: Wednesday, February 19, 2003 7:34 AM To: Multiple recipients of list ORACLE-L Make sure job_queue_processes 0. Also check job_queue_interval. Jay Hostetter Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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_JOB
The PL/SQL packages that we have running as jobs resubmit themselves when the job is complete. So we can just add logic before calling DBMS_JOB.SUBMIT. Can your PL/SQL be modified in this way? Jay [EMAIL PROTECTED] 02/18/03 09:14AM Hi All, Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes, unless the time of day is between X and Y (for example 00:00am 03:00am)? All help appreciated! Pointers to RTFM more than welcome! ;) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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_JOB
use a function: check if 15 minutes after current time is in the don't run interval if it is return the first time after the interval if not return 15 minutes later Pat Hi All, Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes, unless the time of day is between X and Y (for example 00:00am 03:00am)? All help appreciated! Pointers to RTFM more than welcome! ;) Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pat Hildebrand 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_JOB
DOH! You know, I knew that I had read about this somewhere, but couldn't for the life of me remember where. You mentioning Tom kick started my aching brain! ;) The asktom site is down at the moment whilst being upgraded, but I still have the copy of Oracle magazine where Tom discussed this! For all others that may be interested, page 98 of Oracle Magazine (January/February 2003 edition) discusses this (Setting a Complex Interval)! Thanks Kirti! Mark -Original Message- Sent: 18 February 2003 15:09 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Mark, You may want to check http://asktom.oracle.com. I think there are a few examples that you will find come close to what you are looking for. - Kirti -Original Message- Sent: Tuesday, February 18, 2003 8:14 AM To: Multiple recipients of list ORACLE-L Hi All, Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes, unless the time of day is between X and Y (for example 00:00am 03:00am)? All help appreciated! Pointers to RTFM more than welcome! ;) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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: Mark Leith 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_JOB
Mark, You may want to check http://asktom.oracle.com. I think there are a few examples that you will find come close to what you are looking for. - Kirti -Original Message- Sent: Tuesday, February 18, 2003 8:14 AM To: Multiple recipients of list ORACLE-L Hi All, Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes, unless the time of day is between X and Y (for example 00:00am 03:00am)? All help appreciated! Pointers to RTFM more than welcome! ;) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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: Deshpande, Kirti 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_JOB
Just found an online version (in the spirit of sharing solutions ;D):
http://otn.oracle.com/oramag/oracle/03-jan/o13asktom.html
---snip---
Setting a Complex Interval
I am using DBMS_JOB, and I want to schedule a job that runs every 15 minutes
from Monday to Friday, between 6 a.m. and 6 p.m. How do I schedule it? I
cannot figure out the interval I should be passing.
Well, for figuring out complex intervals for DBMS_JOB, I like to use the new
(as of Oracle8i Release 2) CASE statement. For example, the following CASE
statement returns the correct interval for your specification:
SQL alter session set nls_date_format =
2 'dy mon dd, hh24:mi';
Session altered.
SQL select
2 sysdate,
3 case
4when (to_char( sysdate, 'hh24' )
5 between 6 and 17
6 and to_char(sysdate,'dy') NOT IN
7 ('sat','sun') )
8then trunc(sysdate)+
9 (trunc(to_char(sysdate,'s')/
10 900)+1)*15/24/60
11when (to_char( sysdate, 'dy' )
12 not in ('fri','sat','sun') )
13then trunc(sysdate)+1+6/24
14else next_day( trunc(sysdate),
15 'Mon' )+6/24
16 end interval_date
17 from dual
18 /
SYSDATE
--
INTERVAL_DATE
--
sun sep 15, 2002 16:35
mon sep 16, 2002 06:00
The CASE statement gives you great flexibility in generating a complex value
such as you need. Unfortunately, DBMS_JOB will allow you to use only an
interval that is 200 characters or less, and even if you scrunch up the
CASE statement above, you'll find it is about 300 characters minimally. So,
you cannot use it directly in the call to DBMS_JOB. My solution to that is
one of two things: either I would create a view NEXT_DATE as that select, so
that select * from next_date would return the next time the job runs, or I
would wrap the above query in a PL/SQL function that returns a date. If I
used a view, my call to DBMS_JOB might look like:
begin
dbms_job.submit
( :n, 'proc;', sysdate,
'(select * from next_date)'
);
end;
/
Or, if I used the PL/SQL function approach and created a function NEXT_DATE,
it could be:
begin
dbms_job.submit
( :n, 'proc;', sysdate,
'next_date()'
);
end;
/
---snip---
Cheers
Mark
-Original Message-
Sent: 18 February 2003 15:29
To: Multiple recipients of list ORACLE-L
DOH!
You know, I knew that I had read about this somewhere, but couldn't for the
life of me remember where. You mentioning Tom kick started my aching brain!
;)
The asktom site is down at the moment whilst being upgraded, but I still
have the copy of Oracle magazine where Tom discussed this! For all others
that may be interested, page 98 of Oracle Magazine (January/February 2003
edition) discusses this (Setting a Complex Interval)!
Thanks Kirti!
Mark
-Original Message-
Sent: 18 February 2003 15:09
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Mark,
You may want to check http://asktom.oracle.com.
I think there are a few examples that you will find come close to what you
are looking for.
- Kirti
-Original Message-
Sent: Tuesday, February 18, 2003 8:14 AM
To: Multiple recipients of list ORACLE-L
Hi All,
Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes,
unless the time of day is between X and Y (for example 00:00am 03:00am)?
All help appreciated! Pointers to RTFM more than welcome! ;)
Mark
===
Mark Leith | T: +44 (0)1905 330 281
Sales Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: [EMAIL PROTECTED]
===
http://www.cool-tools.co.uk
Maximising throughput performance
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Leith
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: Mark Leith
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
RE: DBMS_JOB : Can submit but does not execute
Hi Gurus,
I use the following command to execute immediately.
Connected.
SQL BEGIN
2 DBMS_JOB.RUN(1);
3 END;
4 /
PL/SQL procedure successfully completed.
--
-- I change the time for next execution to check whether it can submit on 18 Feb 2003
at 8am
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 ' PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
It did not execute. Any advice ? TIA
Regds,
Catherine
-Original Message-
From: CHAN Chor Ling Catherine (CSC)
Sent: Wednesday, February 19, 2003 9:15 AM
To: '[EMAIL PROTECTED]'
Subject:DBMS_JOB : Can submit but does not execute
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner
require any privileges ? However, I can use DBMS_JOB.CHANGE command to execute
immediately. I would like the job to execute on a daily basis. Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USERLAST_DATE LAST_SEC THIS_DATE THIS_SEC
NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - -
- -- -
INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV
INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:20
18-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENCY='AMERICA' NLS_NUME
RIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-YY'
NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
01020002
0
Regds,
Catherine
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: CHAN Chor Ling Catherine (CSC)
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: RE: dbms_job - running jobs every 15 minutes
How about dbms_job.submit(:jobno, 'statspack.snap', sysdate+n/1440, 'sysdate 15/1440'); where n= a number of minutes to the nearest 15 minutes. So if it's 14:25 then it would be sysdate+5/1440. since you only need to do this one time, just make sure that sysdate + n = 0, 15, 30 or 45 after the hour... :-) Of course, if you want to automate the thing, then build this around a PL/SQL procedure...that calculates the value of n. Not elegant, but I think that when someone looks at DBA_JOBS they are not going to ask what the $*#(@( you were trying to do.. I subscribe to the KISS philosophy... :-) RF -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Robert, I am afraid that you will soon run into the 'slipping job' syndrom. Without any 'trunc' (or anything functionally similar), 'sysdate' in the interval happens to be the date when the job started - which may be up to one minute (usually) the time when you asked it to start. Means that you can easily slip by four minutes every hour. I agree with adding 15/1440 (one day = 1440mn), but your base time musn't be 'sysdate' but sysdate rounded to the nearest quarter of an hour. Considering that a quarter of an hour is a 96th (24 * 4) of a day you have several more or less complicated ways to do it. Vladimir (whose formula I am still trying to understand :-)) took the seconds since midnight, you can also do something such as [today 00:00] trunc(sysdate) + [current time rounded to the latest quarter of an hour] floor((sysdate - +trunc(sysdate))* 96) / 96 + 15/1440 HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul 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_job - running jobs every 15 minutes
Personally, I tend to just submit four jobs: one at the top of hour, one at 15 past, one at 30 past, and the fourth at 45 past. To alter the frequency, just break or remove one or more of the jobs. Falls into the category of not elegant, but it works... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 9:39 PM How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Robert Freeman 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: Tim Gorman 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_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared *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.*1
Re: dbms_job - running jobs every 15 minutes
That's exactly what I do. Phew! I thought I was the only one ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 8:03 AM Personally, I tend to just submit four jobs: one at the top of hour, one at 15 past, one at 30 past, and the fourth at 45 past. To alter the frequency, just break or remove one or more of the jobs. Falls into the category of not elegant, but it works... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 9:39 PM How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Robert Freeman 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: Tim Gorman 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: Arup Nanda 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_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes Cron? How 1980's :-)) RF Robert G. FreemanTechnical Management ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of several books you can find on Amazon.com! -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 7:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes Robert, I have solid reasons not to trust dbms_job ... it didn't work reliably in 901x. Call me retro ... but "cron" rocks ... 8:) Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes Cron? How 1980's :-)) RF 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_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes Cron? How RELIABLE !! -Original Message-From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes Cron? How 1980's :-)) RF Robert G. FreemanTechnical Management ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of several books you can find on Amazon.com! -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 7:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes LOL. Ok, I confess, my name's Robert and I'm a CRON user Rf Robert G. FreemanTechnical Management ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of several books you can find on Amazon.com! -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:49 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes Cron? How RELIABLE !! -Original Message-From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes Cron? How 1980's :-)) RF Robert G. FreemanTechnical Management ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of several books you can find on Amazon.com! -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 7:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared
RE: dbms_job - running jobs every 15 minutes
One thing I've learned Arup, there are 15,000 ways of doing the same thing, and a good many of those are as good as the other. Cron, dbms_job, at, whatever works for you!! Rf Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (it's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Wednesday, January 22, 2003 8:55 AM To: Multiple recipients of list ORACLE-L That's exactly what I do. Phew! I thought I was the only one ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 8:03 AM Personally, I tend to just submit four jobs: one at the top of hour, one at 15 past, one at 30 past, and the fourth at 45 past. To alter the frequency, just break or remove one or more of the jobs. Falls into the category of not elegant, but it works... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 9:39 PM How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Robert Freeman 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: Tim Gorman 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: Arup Nanda 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
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes
LOL. You are right about the job_scheduler in early
9i.
Had
all sorts of problems when I first started moving stuff to
9.0.1
and 9.0.2 and Oracle was no help figuring out what the
problem was.
I use
cron all the time, but I love the job scheduler for things
like
starting parallel PL/SQL threads, etc It's just so easy!
;-)
Hey, I
*LIKE* retro, listen to the 80's station all the time!
RF
Robert G. FreemanTechnical Management
ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's
everywhere that I am!)Author of several books you can find on
Amazon.com!
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22,
2003 9:49 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: dbms_job - running jobs every 15
minutes
Robert,
I have solid reasons not to trust dbms_job ... it didn't work reliably
in 901x. Call me retro ... but "cron" rocks ...
8:)
Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN
dot com
Any opinion expressed here is
personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts,
but having an opinion is an
art!
-Original Message-From: Freeman Robert - IL
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
dbms_job - running jobs every 15 minutes
Cron? How 1980's :-))
RF
Re: dbms_job - running jobs every 15 minutes
[EMAIL PROTECTED] wrote: One potential problem with DBMS_JOBS as is being discussed here is that Oracle computes the next_date at the end of the job. They do that so that if a job -- INTERVAL is a date function, evaluated immediately before the job starts -- executing... runs longer than it's schedule interval the two invocations will not run into each other. Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: RE: dbms_job - running jobs every 15 minutes
Yea... I realized that after I sent the email, so I submitted my second suggestion... Thanks though for your thoughts!! Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (It's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Wednesday, January 22, 2003 2:45 AM To: Multiple recipients of list ORACLE-L How about dbms_job.submit(:jobno, 'statspack.snap', sysdate+n/1440, 'sysdate 15/1440'); where n= a number of minutes to the nearest 15 minutes. So if it's 14:25 then it would be sysdate+5/1440. since you only need to do this one time, just make sure that sysdate + n = 0, 15, 30 or 45 after the hour... :-) Of course, if you want to automate the thing, then build this around a PL/SQL procedure...that calculates the value of n. Not elegant, but I think that when someone looks at DBA_JOBS they are not going to ask what the $*#(@( you were trying to do.. I subscribe to the KISS philosophy... :-) RF -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Robert, I am afraid that you will soon run into the 'slipping job' syndrom. Without any 'trunc' (or anything functionally similar), 'sysdate' in the interval happens to be the date when the job started - which may be up to one minute (usually) the time when you asked it to start. Means that you can easily slip by four minutes every hour. I agree with adding 15/1440 (one day = 1440mn), but your base time musn't be 'sysdate' but sysdate rounded to the nearest quarter of an hour. Considering that a quarter of an hour is a 96th (24 * 4) of a day you have several more or less complicated ways to do it. Vladimir (whose formula I am still trying to understand :-)) took the seconds since midnight, you can also do something such as [today 00:00] trunc(sysdate) + [current time rounded to the latest quarter of an hour] floor((sysdate - trunc(sysdate))* 96) / 96 + 15/1440 HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul 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: Freeman Robert - IL 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_job - running jobs every 15 minutes
Well, I did consider that using 4 jobs , but my overdeveloped sense of aesthetics and professional hubris (that one mostly) required that it be done with a single job. ;) Lots of good suggestions here. Seems like everyone is always up for a puzzle. :) Jared Arup Nanda [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/22/2003 06:54 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: dbms_job - running jobs every 15 minutes That's exactly what I do. Phew! I thought I was the only one ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 8:03 AM Personally, I tend to just submit four jobs: one at the top of hour, one at 15 past, one at 30 past, and the fourth at 45 past. To alter the frequency, just break or remove one or more of the jobs. Falls into the category of not elegant, but it works... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 9:39 PM How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Robert Freeman 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: Tim Gorman 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: Arup Nanda 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
RE: dbms_job - running jobs every 15 minutes
Thanks Robert, I like this. Simplified and still easy to read. Jared Robert Freeman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2003 08:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: dbms_job - running jobs every 15 minutes How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Robert Freeman 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: 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: RE: dbms_job - running jobs every 15 minutes
Hmm... I had only given the code a cursory examination. Good thing I didn't use it. :) Jared Freeman Robert - IL [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/22/2003 10:19 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: dbms_job - running jobs every 15 minutes Yea... I realized that after I sent the email, so I submitted my second suggestion... Thanks though for your thoughts!! Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (It's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Wednesday, January 22, 2003 2:45 AM To: Multiple recipients of list ORACLE-L How about dbms_job.submit(:jobno, 'statspack.snap', sysdate+n/1440, 'sysdate 15/1440'); where n= a number of minutes to the nearest 15 minutes. So if it's 14:25 then it would be sysdate+5/1440. since you only need to do this one time, just make sure that sysdate + n = 0, 15, 30 or 45 after the hour... :-) Of course, if you want to automate the thing, then build this around a PL/SQL procedure...that calculates the value of n. Not elegant, but I think that when someone looks at DBA_JOBS they are not going to ask what the $*#(@( you were trying to do.. I subscribe to the KISS philosophy... :-) RF -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Robert, I am afraid that you will soon run into the 'slipping job' syndrom. Without any 'trunc' (or anything functionally similar), 'sysdate' in the interval happens to be the date when the job started - which may be up to one minute (usually) the time when you asked it to start. Means that you can easily slip by four minutes every hour. I agree with adding 15/1440 (one day = 1440mn), but your base time musn't be 'sysdate' but sysdate rounded to the nearest quarter of an hour. Considering that a quarter of an hour is a 96th (24 * 4) of a day you have several more or less complicated ways to do it. Vladimir (whose formula I am still trying to understand :-)) took the seconds since midnight, you can also do something such as [today 00:00] trunc(sysdate) + [current time rounded to the latest quarter of an hour] floor((sysdate - trunc(sysdate))* 96) / 96 + 15/1440 HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul 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: Freeman Robert - IL 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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
RE: dbms_job - running jobs every 15 minutes
Thanks! Works better than the first suggestion. RF Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (It's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Wednesday, January 22, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Thanks Robert, I like this. Simplified and still easy to read. Jared Robert Freeman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2003 08:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: dbms_job - running jobs every 15 minutes How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Robert Freeman 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: 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: Freeman Robert - IL 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_job - running jobs every 15 minutes
[EMAIL PROTECTED] wrote: Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. No, as written, my jobs start on every quarter hour, regardless of runtime. e.g. 09:00, 09:15, 09:30, 09:45 ... Jared, I wanted to ask this question before but just provided a solution w/o talking too much :) If it's regardless of runtime (it means potentially one job can consume more than 15 minutes to get things done) is it allowed to run jobs concurrently? In case of positive asnwer, you need 4 jobs. Otherwise in case of more than 15mins runtime you'll face slipped jobs. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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_job - running jobs every 15 minutes
As an aside, if you want something to run twice a day at the same time (in my case 11:50 AM/PM), this seems to work well for an interval: trunc(sysdate) + (decode(to_char(sysdate,'AM'),'AM',1,2)*12+(11+(50/60)))/24 Enjoy! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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_job - running jobs every 15 minutes
The problem with that formula is that you'll get creep and eventually your 15-minute jobs are running 8 minutes late. Try this one instead: trunc(sysdate) + (trunc(to_number(sysdate - trunc(sysdate)) * (60/15*24)) / (60/15*24)) + 1/(60/15*24) Guaranteed to run every 15 on the 15. At least I think so... The algorithm comes courtesy the TOAD group from Quest, via the TOAD mailling list, and us here at QTI. Enjoy! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 22, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: dbms_job - running jobs every 15 minutes Thanks Robert, I like this. Simplified and still easy to read. Jared Robert Freeman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2003 08:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: dbms_job - running jobs every 15 minutes How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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_job - running jobs every 15 minutes
If a job takes longer than the next scheduled time to execute then I see a problem. Suppose a job runs every 15 mins but runtime is 30 mins, the number ofjobs will increase andcompete for the same resources. I always use cron (80's kinda of control), and the 1st thing I do is check if the job is running and if it is, I exit and issue a warning email. Gene [EMAIL PROTECTED] 01/22/03 02:34PM [EMAIL PROTECTED] wrote:Now as discussed, if the job is scheduled to start at 9:00 AM andrunbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. No, as written, my jobs start on every quarter hour, regardless of runtime. e.g. 09:00, 09:15, 09:30, 09:45 ...Jared, I wanted to ask this question before but just provideda solution w/o talking too much :)If it's regardless of runtime (it means potentially one jobcan consume more than 15 minutes to get things done) is itallowed to run jobs concurrently?In case of positive asnwer, you need 4 jobs. Otherwise in caseof more than 15mins runtime you'll face slipped jobs.Regards,-- Vladimir BegunThe statements and opinions expressed here are my own anddo not necessarily represent those of Oracle Corporation.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Vladimir Begun INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: dbms_job - running jobs every 15 minutes
I like this solution.. Looking at the second one I proposed, I think that it actually would end up just running once an hour at 15 after It's amazing how things look a lot different at midnight and 2pm in the afternoon. Cheers! Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (it's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Wednesday, January 22, 2003 1:39 PM To: Multiple recipients of list ORACLE-L The problem with that formula is that you'll get creep and eventually your 15-minute jobs are running 8 minutes late. Try this one instead: trunc(sysdate) + (trunc(to_number(sysdate - trunc(sysdate)) * (60/15*24)) / (60/15*24)) + 1/(60/15*24) Guaranteed to run every 15 on the 15. At least I think so... The algorithm comes courtesy the TOAD group from Quest, via the TOAD mailling list, and us here at QTI. Enjoy! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 22, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: dbms_job - running jobs every 15 minutes Thanks Robert, I like this. Simplified and still easy to read. Jared Robert Freeman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2003 08:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: dbms_job - running jobs every 15 minutes How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Freeman Robert - IL 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_job - running jobs every 15 minutes
1980's??? Yes, but it works, ain't broke, reliable, KISS, and is easy to implement. I use cron from my Linux box to connect to the production Oracle database (OpenVMS) and do a RMAN backup with the catalog on a second Oracle database (OpenVMS) server. Beats the heck out of trying to get the backup scritp to rewrite its' self after each use. If either server has problems there are entries in the log. Ron [EMAIL PROTECTED] 01/22/03 10:09AM Cron? How 1980's :-)) RF Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (it's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Wednesday, January 22, 2003 7:19 AM To: Multiple recipients of list ORACLE-L I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers 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_job - running jobs every 15 minutes
Stephane Faroult wrote:
Vladimir (whose formula I am still trying to understand :-))...
TRUNC(SYSDATE) + (CEIL(TO_CHAR(SYSDATE, 'S') / 60 / :interval) / (24
* 60 / :interval));
P.S.: could you please answer my question ('100% CPU utilization,
urgent') thread?
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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_job - running jobs every 15 minutes
Vladamir, This job runs pretty fast, so I don't worry about jobs running into each other. It's a level 0 statspack.snapshot, it has run 407 seconds in ~ 700 executions. Jared Vladimir Begun [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/22/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: dbms_job - running jobs every 15 minutes [EMAIL PROTECTED] wrote: Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. No, as written, my jobs start on every quarter hour, regardless of runtime. e.g. 09:00, 09:15, 09:30, 09:45 ... Jared, I wanted to ask this question before but just provided a solution w/o talking too much :) If it's regardless of runtime (it means potentially one job can consume more than 15 minutes to get things done) is it allowed to run jobs concurrently? In case of positive asnwer, you need 4 jobs. Otherwise in case of more than 15mins runtime you'll face slipped jobs. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: 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_job - running jobs every 15 minutes
Jared in general it's much more better to use bind variables, here I do not use them, shame on me! :) Hope the code below is ok for you at least it does not look so complicated. VARIABLE jobno NUMBER; VARIABLE plsql VARCHAR2(1000); EXEC :plsql := 'BEGIN statspack.snap; END;'; BEGIN dbms_job.submit( :jobno , :plsql , TRUNC(SYSDATE) + (CEIL(TO_CHAR(SYSDATE, 'S') / 900) / 96) , 'TRUNC(SYSDATE) + (CEIL(TO_CHAR(SYSDATE, ''S'') / 900) / 96)' ); COMMIT; END; / -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. [EMAIL PROTECTED] wrote: Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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_job - running jobs every 15 minutes
How about dbms_job.submit(:jobno, 'statspack.snap', sysdate+n/1440, 'sysdate 15/1440'); where n= a number of minutes to the nearest 15 minutes. So if it's 14:25 then it would be sysdate+5/1440. since you only need to do this one time, just make sure that sysdate + n = 0, 15, 30 or 45 after the hour... :-) Of course, if you want to automate the thing, then build this around a PL/SQL procedure...that calculates the value of n. Not elegant, but I think that when someone looks at DBA_JOBS they are not going to ask what the $*#(@( you were trying to do.. I subscribe to the KISS philosophy... :-) RF -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Robert Freeman 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_job - running jobs every 15 minutes
How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Robert Freeman 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_job interval 10till
Barbara Baker wrote:
Sun / Solaris 2.6 ; Oracle RDBMS v8.0.5.2.1
List:
I want to change the perfstat statspack.snap procedure
FROM running every hour on-the-hour
TO running at 10 minutes till the hour.
(On-the-hour is conflicting with another procedure.)
I tried to Read The Frustrating Manual, but I can only
find examples for on-the-hour intervals.
I thought I had it nailed when I spoon-fed the job
with the next_date value at 10 till, and then the
interval for 'SYSDATE+1/24'. When I looked at it
after the next run, it was fine, but it has creeped.
Now it's running whenever it feels like it.
I've lost track of all the date intervals I tried, but
here's what I tried last.
variable jobno number;
begin
select 1 into :jobno from dual;
end;
/
begin
dbms_job.change(:jobno,
'statspack.snap;',
to_date('09-JAN-2003 9:50') ,
'SYSDATE+1/24'
);
commit;
end;
/
prompt Job number for automated statistics collection
for this instance
prompt
prompt Note that this job number is needed when
modifying or removing
prompt the job:
print jobno
Here's what it currently looks like:
What: statspack.snap;
Job: 1
Priv User: PERFSTAT
Last: 01/10/2003 14:16:13
Running: NO
Next: 01/10/2003 15:16:13
Interval: SYSDATE+1/24
Total Time: 53985.99
Broken:
Any idea how I can get to run consistently at 10 till
each hour?
Thanks in advance for any help.
Barb
Barbara,
Jobs creep because Oracle polls for jobs every minute by default -
more or less. Then it computes 'next run' time, runs the job and, if it
works, updates next_date. The problem is that you job starts say only 20
seconds late (based on the 'theoretical' time) this lag accumulates if
you compute the next time as SYSDATE + 1/24. Assume 20 s each time on
average, it's 8 minutes every day - quickly becomes quite noticeable.
The only way to keep things under check is to use TRUNC() to the
suitable unit. You should define INTERVAL as
TRUNC(SYSDATE, 'HH24') + 1/24 + 10/1440
^ round time (current)
^ plus one hour
^ plus ten minutes
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
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_job interval 10till
Stephane:
Precisely what I was after!
Thanks so much for your help. I really appreciate it.
Barb
--- Stephane Faroult [EMAIL PROTECTED] wrote:
Barbara Baker wrote:
Sun / Solaris 2.6 ; Oracle RDBMS v8.0.5.2.1
List:
I want to change the perfstat statspack.snap
procedure
FROM running every hour on-the-hour
TO running at 10 minutes till the hour.
(On-the-hour is conflicting with another
procedure.)
I tried to Read The Frustrating Manual, but I can
only
find examples for on-the-hour intervals.
I thought I had it nailed when I spoon-fed the job
with the next_date value at 10 till, and then the
interval for 'SYSDATE+1/24'. When I looked at it
after the next run, it was fine, but it has
creeped.
Now it's running whenever it feels like it.
I've lost track of all the date intervals I tried,
but
here's what I tried last.
variable jobno number;
begin
select 1 into :jobno from dual;
end;
/
begin
dbms_job.change(:jobno,
'statspack.snap;',
to_date('09-JAN-2003 9:50') ,
'SYSDATE+1/24'
);
commit;
end;
/
prompt Job number for automated statistics
collection
for this instance
prompt
prompt Note that this job number is needed when
modifying or removing
prompt the job:
print jobno
Here's what it currently looks like:
What: statspack.snap;
Job: 1
Priv User: PERFSTAT
Last: 01/10/2003 14:16:13
Running: NO
Next: 01/10/2003 15:16:13
Interval: SYSDATE+1/24
Total Time:
53985.99
Broken:
Any idea how I can get to run consistently at 10
till
each hour?
Thanks in advance for any help.
Barb
Barbara,
Jobs creep because Oracle polls for jobs every
minute by default -
more or less. Then it computes 'next run' time, runs
the job and, if it
works, updates next_date. The problem is that you
job starts say only 20
seconds late (based on the 'theoretical' time) this
lag accumulates if
you compute the next time as SYSDATE + 1/24. Assume
20 s each time on
average, it's 8 minutes every day - quickly becomes
quite noticeable.
The only way to keep things under check is to use
TRUNC() to the
suitable unit. You should define INTERVAL as
TRUNC(SYSDATE, 'HH24') + 1/24 + 10/1440
^ round time (current)
^ plus one hour
^ plus ten
minutes
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Stephane Faroult
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).
__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Barbara Baker
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_job interval 10till
Does this work for you:
select trunc(sysdate + 1/24,'hh') + 50/(60 * 24)from dual;
Your next will be: 'trunc(sysdate + 1/24,'hh') + 50/(60 * 24)'
Regards,
Waleed
-Original Message-
Sent: Friday, January 10, 2003 9:09 PM
To: Multiple recipients of list ORACLE-L
Stephane:
Precisely what I was after!
Thanks so much for your help. I really appreciate it.
Barb
--- Stephane Faroult [EMAIL PROTECTED] wrote:
Barbara Baker wrote:
Sun / Solaris 2.6 ; Oracle RDBMS v8.0.5.2.1
List:
I want to change the perfstat statspack.snap
procedure
FROM running every hour on-the-hour
TO running at 10 minutes till the hour.
(On-the-hour is conflicting with another
procedure.)
I tried to Read The Frustrating Manual, but I can
only
find examples for on-the-hour intervals.
I thought I had it nailed when I spoon-fed the job
with the next_date value at 10 till, and then the
interval for 'SYSDATE+1/24'. When I looked at it
after the next run, it was fine, but it has
creeped.
Now it's running whenever it feels like it.
I've lost track of all the date intervals I tried,
but
here's what I tried last.
variable jobno number;
begin
select 1 into :jobno from dual;
end;
/
begin
dbms_job.change(:jobno,
'statspack.snap;',
to_date('09-JAN-2003 9:50') ,
'SYSDATE+1/24'
);
commit;
end;
/
prompt Job number for automated statistics
collection
for this instance
prompt
prompt Note that this job number is needed when
modifying or removing
prompt the job:
print jobno
Here's what it currently looks like:
What: statspack.snap;
Job: 1
Priv User: PERFSTAT
Last: 01/10/2003 14:16:13
Running: NO
Next: 01/10/2003 15:16:13
Interval: SYSDATE+1/24
Total Time:
53985.99
Broken:
Any idea how I can get to run consistently at 10
till
each hour?
Thanks in advance for any help.
Barb
Barbara,
Jobs creep because Oracle polls for jobs every
minute by default -
more or less. Then it computes 'next run' time, runs
the job and, if it
works, updates next_date. The problem is that you
job starts say only 20
seconds late (based on the 'theoretical' time) this
lag accumulates if
you compute the next time as SYSDATE + 1/24. Assume
20 s each time on
average, it's 8 minutes every day - quickly becomes
quite noticeable.
The only way to keep things under check is to use
TRUNC() to the
suitable unit. You should define INTERVAL as
TRUNC(SYSDATE, 'HH24') + 1/24 + 10/1440
^ round time (current)
^ plus one hour
^ plus ten
minutes
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Stephane Faroult
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).
__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Barbara Baker
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: Khedr, Waleed
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
Re: DBMS_JOB problem
Bill, Try putting a ';' after snap that may fix it. SQL variable x number; SQL begin 2 dbms_job.submit(:x,'statspack.snap;',sysdate,'sysdate + 1/24'); 3 end; 4 / I'm trying to use DBMS_JOB to schedule hourly statspack snaps. Per note on Metalink, I've try to run the following: SQL variable x number; SQL begin 2 dbms_job.submit(:x,'statspack.snap',sysdate,'sysdate + 1/24'); 3 end; 4 / begin * ERROR at line 1: ORA-06550: line 1, column 108: PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( @ % ; The symbol ";" was substituted for "END" to continue. ORA-06512: at "SYS.DBMS_JOB", line 79 ORA-06512: at "SYS.DBMS_JOB", line 131 ORA-06512: at line 2 Any ideas on this error? No helpful info on Metalink. Checked the status of the package, too: 1* select object_type, status from all_objects where object_name = 'DBMS_JOB' SQL / OBJECT_TYPE STATUS -- --- PACKAGE VALID SYNONYM VALID Thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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_JOB interval wierdness
Yup, Kevin, it was the job erroring that was causing it to resubmit itself every 24 hours. At least after 11 or so failures, it did. I threw in some error trapping, and all's well. Thanks! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: kkennedy [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 1:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB interval wierdness When the job aborts, it increments the failures column (until it hits the broken point at 16 failures) and resubmits itself with an internally calculated restart time. From the Oracle Administrators Guide: If a job returns an error while Oracle is attempting to execute it, Oracle tries to execute it again. The first attempt is made after one minute, the second attempt after two minutes, the third after four minutes, and so on, with the interval doubling between each attempt. When the retry interval exceeds the execution interval, Oracle continues to retry the job at the normal execution interval. However, if the job fails 16 times, Oracle automatically marks the job as broken and no longer tries to execute it. Works that way here. I can't say why yours reruns every day at 1PM. I can say that if you trap the error and get a normal completion, your problem is solved. Kevin Kennedy First Point Energy Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB interval wierdness
Nope. 1 PM just like it should, only every day instead of every week. I've tried it with the 7 in and out of the TRUNC with the same results. One other thing I just noticed is that the job returns an error each time it is run due to bug in DBMS_STATS. I'm rectifying that now with trapping and an error table, but I'm still confused as to why it would resub in a day instead of the interval. Thx! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 11:31 AM To: Jesse, Rich; Multiple recipients of list ORACLE-L Subject: Re:DBMS_JOB interval wierdness Jesse, No joke, let me guess, 8am every day, right? Try TRUNC(SYSDATE+7)+1/24 instead. Dick Goulet Reply Separator Subject:DBMS_JOB interval wierdness Author: Jesse; Rich [EMAIL PROTECTED] Date: 7/16/2002 9:08 AM Hey all, I've got a job to gather stats on a schema of an 8.1.6.0.0 DB. The interval of the DBMS_JOB is: TRUNC(SYSDATE)+7+1/24 ...but it keeps resubmitting itself every day, instead of once a week. I *must* be brain dead or this cold's getting the best of me, because I just can't see what I'm missing. Anyone? Bueller? Bueller? Rich Jesse -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB interval wierdness
When the job aborts, it increments the failures column (until it hits the broken point at 16 failures) and resubmits itself with an internally calculated restart time. From the Oracle Administrators Guide: If a job returns an error while Oracle is attempting to execute it, Oracle tries to execute it again. The first attempt is made after one minute, the second attempt after two minutes, the third after four minutes, and so on, with the interval doubling between each attempt. When the retry interval exceeds the execution interval, Oracle continues to retry the job at the normal execution interval. However, if the job fails 16 times, Oracle automatically marks the job as broken and no longer tries to execute it. Works that way here. I can't say why yours reruns every day at 1PM. I can say that if you trap the error and get a normal completion, your problem is solved. Kevin Kennedy First Point Energy Corporation -Original Message- Sent: Tuesday, July 16, 2002 11:26 AM To: Multiple recipients of list ORACLE-L Nope. 1 PM just like it should, only every day instead of every week. I've tried it with the 7 in and out of the TRUNC with the same results. One other thing I just noticed is that the job returns an error each time it is run due to bug in DBMS_STATS. I'm rectifying that now with trapping and an error table, but I'm still confused as to why it would resub in a day instead of the interval. Thx! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 11:31 AM To: Jesse, Rich; Multiple recipients of list ORACLE-L Subject: Re:DBMS_JOB interval wierdness Jesse, No joke, let me guess, 8am every day, right? Try TRUNC(SYSDATE+7)+1/24 instead. Dick Goulet Reply Separator Subject:DBMS_JOB interval wierdness Author: Jesse; Rich [EMAIL PROTECTED] Date: 7/16/2002 9:08 AM Hey all, I've got a job to gather stats on a schema of an 8.1.6.0.0 DB. The interval of the DBMS_JOB is: TRUNC(SYSDATE)+7+1/24 ...but it keeps resubmitting itself every day, instead of once a week. I *must* be brain dead or this cold's getting the best of me, because I just can't see what I'm missing. Anyone? Bueller? Bueller? Rich Jesse -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: kkennedy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: RE: DBMS_JOB interval wierdness
Yes and yes. Unfortunately, I don't have the time to setup the workaround for the GATHER_SCHEMA_STATS bug. It'll hit a NO_DATA_FOUND after all objects have been analyzed. I've setup some error trapping and left the job alone to see if it runs without the error. And if there's no error, will it submit at the proper interval or not. Of course it runs at 1 *AM* and not *PM*, like I had said, so I'll wait to see what happens tomorrow. Thx! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 12:53 PM To: Jesse, Rich; Multiple recipients of list ORACLE-L Subject: Re:RE: DBMS_JOB interval wierdness Jesse, Is this 8.1.6? If so there's a bug filed against dbms_stats. Dick Goulet Reply Separator Subject:RE: DBMS_JOB interval wierdness Author: Jesse; Rich [EMAIL PROTECTED] Date: 7/16/2002 12:24 PM Nope. 1 PM just like it should, only every day instead of every week. I've tried it with the 7 in and out of the TRUNC with the same results. One other thing I just noticed is that the job returns an error each time it is run due to bug in DBMS_STATS. I'm rectifying that now with trapping and an error table, but I'm still confused as to why it would resub in a day instead of the interval. Thx! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 11:31 AM To: Jesse, Rich; Multiple recipients of list ORACLE-L Subject: Re:DBMS_JOB interval wierdness Jesse, No joke, let me guess, 8am every day, right? Try TRUNC(SYSDATE+7)+1/24 instead. Dick Goulet Reply Separator Subject:DBMS_JOB interval wierdness Author: Jesse; Rich [EMAIL PROTECTED] Date: 7/16/2002 9:08 AM Hey all, I've got a job to gather stats on a schema of an 8.1.6.0.0 DB. The interval of the DBMS_JOB is: TRUNC(SYSDATE)+7+1/24 ...but it keeps resubmitting itself every day, instead of once a week. I *must* be brain dead or this cold's getting the best of me, because I just can't see what I'm missing. Anyone? Bueller? Bueller? Rich Jesse -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB does not run on NT
Hi I had same situation before. Check owner of the job and security and add necessary privilege. Mitchell - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, July 14, 2002 10:33 AM Dear gurus ! I submit a job via the DBMS_JOB package , it looks OK , select from user_jobs shows that broken='N' . When i execute the job manually , using dbms_job.run , it runs , but it does not run automatically (each 5 minutes , as i have specified when submitting the job). Any guess ? Thanks a lot ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: mitchell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB does not run on NT
Have you set job_queue_processes and job_queue_interval parameters? If job_queue_processes is zero, the job will always run manually but never automatic. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, July 14, 2002 10:33 AM Dear gurus ! I submit a job via the DBMS_JOB package , it looks OK , select from user_jobs shows that broken='N' . When i execute the job manually , using dbms_job.run , it runs , but it does not run automatically (each 5 minutes , as i have specified when submitting the job). Any guess ? Thanks a lot ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] *2 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_JOB does not run on NT
Andrey, Are you issuing a commit after your dbms_job.submit? jack --- Andrey Bronfin [EMAIL PROTECTED] wrote: Dear gurus ! I submit a job via the DBMS_JOB package , it looks OK , select from user_jobs shows that broken='N' . When i execute the job manually , using dbms_job.run , it runs , but it does not run automatically (each 5 minutes , as i have specified when submitting the job). Any guess ? Thanks a lot ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB does not run on NT
Check if you have a non-zero value for JOB_QUEUE_PROCESSES? HTH CP [EMAIL PROTECTED] wrote: Dear gurus ! I submit a job via the DBMS_JOB package , it looks OK , select from user_jobs shows that broken='N' . When i execute the job manually , using dbms_job.run , it runs , but it does not run automatically (each 5 minutes , as i have specified when submitting the job). Any guess ? Thanks a lot ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CP INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB does not run on NT - SOLVED !
No , it was not ;-) Sorry , i knew it , but overlooked it ;-) It's working now ! Thanks a lot to all who replied ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Sun, July 14, 2002 5:51 PM To: Andrey Bronfin; LazyDBA.com Discussion Is job_queue_processes set to greater than zero? Mike -Original Message- Sent: Sunday, July 14, 2002 10:32 AM To: LazyDBA.com Discussion Dear gurus ! I submit a job via the DBMS_JOB package , it looks OK , select from user_jobs shows that broken='N' . When i execute the job manually , using dbms_job.run , it runs , but it does not run automatically (each 5 minutes , as i have specified when submitting the job). Any guess ? Thanks a lot ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_job question
Hi all, I have used the dbms_job package in some cases. Normally I use the INTERVAL method of the packages. i.e. 30 sec. But soemtimes it occurs, that users change their system time to the future to test something, then changing back to the correct time. The INTERVAL job then stops, as he has not reached his future time.. any ideas to handle this ?? (Oracle 7.3.4.1 on NT4) TNX Frank Except forbidding your users to play with time, I don't see any 'clean' way to do this. Basically a job runs and INTERVAL is used to compute the next time when it must run. Job processes wake up periodically and check the system current time against this. If the current time and the previous computation are incoherent, you are done. The best you can do is perhaps a (manually executed) SQL script which checks whether times in DBA_JOBS make sense. HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_job not running
Did you set JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL in your initsid.ora parameter file? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, April 19, 2002 12:13 PM Hi, We have a job own by user MTSSYS which we scheduled to run every 1 minute but it is not running itself. If we run the job manully from sqlplus it runs fine. What can be the reason that job is not running itself??? Interval is set to SYSDATE+1/1440 Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_job not running
If the parameters suggested are already set up right , I have one more thing to ask... 1. is this something do with the DB Link ??? 2. if so , the user running the job is having any private DBLINK ??? --Madhu PS: If the DB LINK is used to run this job , you need to have a private dblink created for the user running the job. -Original Message- Sent: Friday, April 19, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Hi, We have a job own by user MTSSYS which we scheduled to run every 1 minute but it is not running itself. If we run the job manully from sqlplus it runs fine. What can be the reason that job is not running itself??? Interval is set to SYSDATE+1/1440 Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_job not running
Are there any jobs running? There's a couple of init.ora parameters that must be set first. Jim -Original Message- Sent: Friday, April 19, 2002 2:04 PM To: Multiple recipients of list ORACLE-L If the parameters suggested are already set up right , I have one more thing to ask... 1. is this something do with the DB Link ??? 2. if so , the user running the job is having any private DBLINK ??? --Madhu PS: If the DB LINK is used to run this job , you need to have a private dblink created for the user running the job. -Original Message- Sent: Friday, April 19, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Hi, We have a job own by user MTSSYS which we scheduled to run every 1 minute but it is not running itself. If we run the job manully from sqlplus it runs fine. What can be the reason that job is not running itself??? Interval is set to SYSDATE+1/1440 Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Conboy, Jim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_job not running
Did you commit after submitting the job or the session is still open? Waleed -Original Message- Sent: Friday, April 19, 2002 12:14 PM To: Multiple recipients of list ORACLE-L Hi, We have a job own by user MTSSYS which we scheduled to run every 1 minute but it is not running itself. If we run the job manully from sqlplus it runs fine. What can be the reason that job is not running itself??? Interval is set to SYSDATE+1/1440 Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Ang: RE: DBMS_JOB statement
So this means that every day at 6 pm th eprocedure 'procedure' runs. /Roland Thomas, Kevin [EMAIL PROTECTED]@fatcity.com den 2001-09-11 03:30 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Kopia: Hi Roland, declare l_job number; begin dbms_job.submit( l_job, 'procedure;', trunc(sysdate)+18/24, 'trunc(sysdate)+18/24' ); end; This runs at exactly 6pm every day (18 hours into the day, replace procedure with the name of the stored procedure that you want to run. Cheers, Kev hit any user to continue __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. (2nd Floor East - Weirs Building) Tel: 0141 568 2377 Fax: 0141 568 2366 http://www.calanais.com -Original Message- Sent: 11 September 2001 10:00 To: Multiple recipients of list ORACLE-L Hallo, Can anyone tell me a example on a script which does the following: - Starts a job, a pl/sql-procedur, at 6 pm every day, - If something goes wrong then I want information to be logged. (Should I include the logging in the pl/sql procedur) Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: RE: DBMS_JOB statement
Yes, that's correct. Kev. -Original Message- Sent: 12 September 2001 09:45 To: Multiple recipients of list ORACLE-L So this means that every day at 6 pm th eprocedure 'procedure' runs. /Roland Thomas, Kevin [EMAIL PROTECTED]@fatcity.com den 2001-09-11 03:30 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Kopia: Hi Roland, declare l_job number; begin dbms_job.submit( l_job, 'procedure;', trunc(sysdate)+18/24, 'trunc(sysdate)+18/24' ); end; This runs at exactly 6pm every day (18 hours into the day, replace procedure with the name of the stored procedure that you want to run. Cheers, Kev hit any user to continue __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. (2nd Floor East - Weirs Building) Tel: 0141 568 2377 Fax: 0141 568 2366 http://www.calanais.com -Original Message- Sent: 11 September 2001 10:00 To: Multiple recipients of list ORACLE-L Hallo, Can anyone tell me a example on a script which does the following: - Starts a job, a pl/sql-procedur, at 6 pm every day, - If something goes wrong then I want information to be logged. (Should I include the logging in the pl/sql procedur) Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB statement
Hi Roland, declare l_job number; begin dbms_job.submit( l_job, 'procedure;', trunc(sysdate)+18/24, 'trunc(sysdate)+18/24' ); end; This runs at exactly 6pm every day (18 hours into the day, replace procedure with the name of the stored procedure that you want to run. Cheers, Kev hit any user to continue __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. (2nd Floor East - Weirs Building) Tel: 0141 568 2377 Fax: 0141 568 2366 http://www.calanais.com -Original Message- Sent: 11 September 2001 10:00 To: Multiple recipients of list ORACLE-L Hallo, Can anyone tell me a example on a script which does the following: - Starts a job, a pl/sql-procedur, at 6 pm every day, - If something goes wrong then I want information to be logged. (Should I include the logging in the pl/sql procedur) Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arich Henneman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arich Henneman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arich Henneman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB Help
Hi, You need to find the job number, then use DBMS_JOB.REMOVE(). This is all in the documentation: http://technet.oracle.com/doc/oracle8i_816/server.816/a76956/jobq.htm#10 57 Note that the job will complete before being removed from the queue. g -Original Message- Sent: Tuesday, August 28, 2001 7:21 AM To: Multiple recipients of list ORACLE-L Hi All, Can Anyone tell me how to stop a background job initiated through a DBMS_JOB package when it is no longer required? Thanks in advance. Regards Sundar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rangachari Sundar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Guy Hammond INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB Help
Rangachari Sundar wrote: Hi All, Can Anyone tell me how to stop a background job initiated through a DBMS_JOB package when it is no longer required? Thanks in advance. Regards Sundar -- desc dbms_job I believe that this will have some effect: exec dbms_job.isbroken(:job_id,true); - and - you could kill the SNPn process that is executing the job. Use orakill for best results. Just killing the session is too ineffective. If you just kill the SNPn process without breaking the job first, the next SNPn process will start it back up again. disclaimer: posting from memory without reading anything. Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Drake INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB Help
Hola, You can use dbms_job.remove(jobnumber) Suerte !! Ramon E. Estevez [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 809-565-3121 -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Rangachari Sundar Enviado el: Tuesday, 28 August, 2001 1:21 AM Para: Multiple recipients of list ORACLE-L Asunto: DBMS_JOB Help Hi All, Can Anyone tell me how to stop a background job initiated through a DBMS_JOB package when it is no longer required? Thanks in advance. Regards Sundar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rangachari Sundar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB
Roland:
This anonymous PL/SQL block submits a job that will insert a row into
mytable:
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'insert into mytable values (''One'',''Two'',''Three'');'
'SYSDATE, 'SYSDATE + 1');
commit;
END;
/
The SYSDATE parameter says, Run this right now and the SYSDATE+1
parameter says, Then run this again at this same time tomorrow.
Jon Walthour
-Original Message-
[EMAIL PROTECTED]
Sent: Monday, August 27, 2001 5:35 AM
To: Multiple recipients of list ORACLE-L
Hallo,
How can I schedule a pl/sql job, I want this to happen:
Every Sunday at 11 pm I want to do an insert into a table.
How shoudl I write the code in the DBMS_JOB satement.?
Roland S
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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.com
--
Author: Jon Walthour
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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_JOB
See http://www.jlcomp.demon.co.uk/faq/dbms_job.html hth connor --- [EMAIL PROTECTED] wrote: Hallo, How can I schedule a pl/sql job, I want this to happen: Every Sunday at 11 pm I want to do an insert into a table. How shoudl I write the code in the DBMS_JOB satement.? Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB
Hi, Did you do a commit after you submitted the job? Jack Yadav, Shailesh To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) ate.ny.us Subject: RE: DBMS_JOB Sent by: [EMAIL PROTECTED] 06-07-2001 22:20 Please respond to ORACLE-L Mitchell, What did you specify for next_date? Try to set it to something like a second after you setup the job.. It should work and you should not have to execute a new job... I think JOB_QUEUE_PROCESSES is the maximum number of jobs that can run in parallel and JOB_QUEUE_INTERVAL is the seconds after which the processes wake up to execute any job. Shailesh -Original Message- Sent: Friday, July 06, 2001 2:42 PM To: Multiple recipients of list ORACLE-L Dear DBAs I have submitted a job to run at a time but never go. I had to send dbms_job.run(jobno) and then the job will run at defined interval. My question is 1. I have to exec dbms_job.run(jobno) everytime to init the the new job. 2. What is real meaning for JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL at initdb.ora Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mitchell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yadav, Shailesh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden
Re: DBMS_JOB
Thanks Jack. It is working now after commit. Mitchell - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, July 09, 2001 4:00 AM Hi, Did you do a commit after you submitted the job? Jack Yadav, Shailesh To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) ate.ny.us Subject: RE: DBMS_JOB Sent by: [EMAIL PROTECTED] 06-07-2001 22:20 Please respond to ORACLE-L Mitchell, What did you specify for next_date? Try to set it to something like a second after you setup the job.. It should work and you should not have to execute a new job... I think JOB_QUEUE_PROCESSES is the maximum number of jobs that can run in parallel and JOB_QUEUE_INTERVAL is the seconds after which the processes wake up to execute any job. Shailesh -Original Message- Sent: Friday, July 06, 2001 2:42 PM To: Multiple recipients of list ORACLE-L Dear DBAs I have submitted a job to run at a time but never go. I had to send dbms_job.run(jobno) and then the job will run at defined interval. My question is 1. I have to exec dbms_job.run(jobno) everytime to init the the new job. 2. What is real meaning for JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL at initdb.ora Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mitchell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yadav, Shailesh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy
RE: DBMS_JOB
Mitchell, What did you specify for next_date? Try to set it to something like a second after you setup the job.. It should work and you should not have to execute a new job... I think JOB_QUEUE_PROCESSES is the maximum number of jobs that can run in parallel and JOB_QUEUE_INTERVAL is the seconds after which the processes wake up to execute any job. Shailesh -Original Message- Sent: Friday, July 06, 2001 2:42 PM To: Multiple recipients of list ORACLE-L Dear DBAs I have submitted a job to run at a time but never go. I had to send dbms_job.run(jobno) and then the job will run at defined interval. My question is 1. I have to exec dbms_job.run(jobno) everytime to init the the new job. 2. What is real meaning for JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL at initdb.ora Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mitchell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yadav, Shailesh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB
If order to stop those funny date , can you show me the code of your pl/sql and let us try to solve it ? Becoz it seen you try to add in those error date into your next_date ? You might need to create a lookup table to key in those require date,and let it parse to the procedure automatical.. -Original Message- Sent: Saturday, June 30, 2001 6:41 AM To: Multiple recipients of list ORACLE-L Thanks Ray and Dick, This the output, obviously some mistakes(*), how do I stop them from executing, (take them out) LAST_DATE NEXT_DATE -- -- carga_d 29/06/2001 30/06/2001 mueve_h_f 20/02/2001 01/01/4000* carga_d 29/06/2001 30/06/2001 carga_v_fl29/06/2001 06/07/2001 carga_v_fl14/03/2001 01/01/4000* carga_v_f_s 29/06/2001 06/07/2001 carga_v_f_m 05/06/2001 05/07/2001 carga_d 20/03/2001 01/01/4000* carga_d 19/03/2001 01/01/4000* carga_s 20/03/2001 01/01/4000* carga_v 20/03/2001 01/01/4000* mueve_h 29/06/2001 30/06/2001 carga_s 29/06/2001 30/06/2001 carga_v 29/06/2001 30/06/2001 carga_h 29/06/2001 30/06/2001 mueve_h 29/06/2001 30/06/2001 carga_v_fl29/06/2001 06/07/2001 carga_v_fl 02/07/2001 What is the sintax to execute the procedures, some every day, some every week and some two times a week, passing different dates automatically as a parameter. Thanks, Ramon Estevez -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Raymond Lee Meng Hong INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB
Ramon, When a job is broken the next_date is set to 01/01/4000. I'm not sure I understand exactly what your initial request was, but you can alter the next_date and interval for any job using DBMS_JOB.CHANGE (or by calling DBMS_JOB.NEXT_DATE and DBMS_JOB.INTERVAL individually). For examples of specifying different intervals, see Note: 1068369.6 Example: Using DBMS_JOB.SUBMIT to Execute Jobs at Regular Intervals HTH, -- Anita --- Raymond Lee Meng Hong [EMAIL PROTECTED] wrote: If order to stop those funny date , can you show me the code of your pl/sql and let us try to solve it ? Becoz it seen you try to add in those error date into your next_date ? You might need to create a lookup table to key in those require date,and let it parse to the procedure automatical.. -Original Message- Sent: Saturday, June 30, 2001 6:41 AM To: Multiple recipients of list ORACLE-L Thanks Ray and Dick, This the output, obviously some mistakes(*), how do I stop them from executing, (take them out) LAST_DATE NEXT_DATE -- -- carga_d 29/06/2001 30/06/2001 mueve_h_f 20/02/2001 01/01/4000* carga_d 29/06/2001 30/06/2001 carga_v_fl29/06/2001 06/07/2001 carga_v_fl14/03/2001 01/01/4000* carga_v_f_s 29/06/2001 06/07/2001 carga_v_f_m 05/06/2001 05/07/2001 carga_d 20/03/2001 01/01/4000* carga_d 19/03/2001 01/01/4000* carga_s 20/03/2001 01/01/4000* carga_v 20/03/2001 01/01/4000* mueve_h 29/06/2001 30/06/2001 carga_s 29/06/2001 30/06/2001 carga_v 29/06/2001 30/06/2001 carga_h 29/06/2001 30/06/2001 mueve_h 29/06/2001 30/06/2001 carga_v_fl29/06/2001 06/07/2001 carga_v_fl 02/07/2001 What is the sintax to execute the procedures, some every day, some every week and some two times a week, passing different dates automatically as a parameter. Thanks, Ramon Estevez -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Raymond Lee Meng Hong INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB
Thanks to all that reply, I want to clean the queue, erase to the jobs, and reschedule them again Ramon Estevez *809-565-3121 x 225 * [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de A. Bardeen Enviado el: Monday, 02 July, 2001 3:50 AM Para: Multiple recipients of list ORACLE-L Asunto: RE: DBMS_JOB Ramon, When a job is broken the next_date is set to 01/01/4000. I'm not sure I understand exactly what your initial request was, but you can alter the next_date and interval for any job using DBMS_JOB.CHANGE (or by calling DBMS_JOB.NEXT_DATE and DBMS_JOB.INTERVAL individually). For examples of specifying different intervals, see Note: 1068369.6 Example: Using DBMS_JOB.SUBMIT to Execute Jobs at Regular Intervals HTH, -- Anita --- Raymond Lee Meng Hong [EMAIL PROTECTED] wrote: If order to stop those funny date , can you show me the code of your pl/sql and let us try to solve it ? Becoz it seen you try to add in those error date into your next_date ? You might need to create a lookup table to key in those require date,and let it parse to the procedure automatical.. -Original Message- Sent: Saturday, June 30, 2001 6:41 AM To: Multiple recipients of list ORACLE-L Thanks Ray and Dick, This the output, obviously some mistakes(*), how do I stop them from executing, (take them out) LAST_DATE NEXT_DATE -- -- carga_d 29/06/2001 30/06/2001 mueve_h_f 20/02/2001 01/01/4000* carga_d 29/06/2001 30/06/2001 carga_v_fl29/06/2001 06/07/2001 carga_v_fl14/03/2001 01/01/4000* carga_v_f_s 29/06/2001 06/07/2001 carga_v_f_m 05/06/2001 05/07/2001 carga_d 20/03/2001 01/01/4000* carga_d 19/03/2001 01/01/4000* carga_s 20/03/2001 01/01/4000* carga_v 20/03/2001 01/01/4000* mueve_h 29/06/2001 30/06/2001 carga_s 29/06/2001 30/06/2001 carga_v 29/06/2001 30/06/2001 carga_h 29/06/2001 30/06/2001 mueve_h 29/06/2001 30/06/2001 carga_v_fl29/06/2001 06/07/2001 carga_v_fl 02/07/2001 What is the sintax to execute the procedures, some every day, some every week and some two times a week, passing different dates automatically as a parameter. Thanks, Ramon Estevez -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Raymond Lee Meng Hong INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
RE: DBMS_JOB
Thanks again to all that reply I used the procedure run of the package DBMS_JOB to reexecute my jobs and they rescheduled with the correct dates again DMBS_JOB.RUN(JOB_NUMBER) tks Ramon Estevez *809-565-3121 x 225 * [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ramon Estevez Enviado el: Monday, 02 July, 2001 9:01 AM Para: Multiple recipients of list ORACLE-L Asunto: RE: DBMS_JOB Thanks to all that reply, I want to clean the queue, erase to the jobs, and reschedule them again Ramon Estevez *809-565-3121 x 225 * [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de A. Bardeen Enviado el: Monday, 02 July, 2001 3:50 AM Para: Multiple recipients of list ORACLE-L Asunto: RE: DBMS_JOB Ramon, When a job is broken the next_date is set to 01/01/4000. I'm not sure I understand exactly what your initial request was, but you can alter the next_date and interval for any job using DBMS_JOB.CHANGE (or by calling DBMS_JOB.NEXT_DATE and DBMS_JOB.INTERVAL individually). For examples of specifying different intervals, see Note: 1068369.6 Example: Using DBMS_JOB.SUBMIT to Execute Jobs at Regular Intervals HTH, -- Anita --- Raymond Lee Meng Hong [EMAIL PROTECTED] wrote: If order to stop those funny date , can you show me the code of your pl/sql and let us try to solve it ? Becoz it seen you try to add in those error date into your next_date ? You might need to create a lookup table to key in those require date,and let it parse to the procedure automatical.. -Original Message- Sent: Saturday, June 30, 2001 6:41 AM To: Multiple recipients of list ORACLE-L Thanks Ray and Dick, This the output, obviously some mistakes(*), how do I stop them from executing, (take them out) LAST_DATE NEXT_DATE -- -- carga_d 29/06/2001 30/06/2001 mueve_h_f 20/02/2001 01/01/4000* carga_d 29/06/2001 30/06/2001 carga_v_fl29/06/2001 06/07/2001 carga_v_fl14/03/2001 01/01/4000* carga_v_f_s 29/06/2001 06/07/2001 carga_v_f_m 05/06/2001 05/07/2001 carga_d 20/03/2001 01/01/4000* carga_d 19/03/2001 01/01/4000* carga_s 20/03/2001 01/01/4000* carga_v 20/03/2001 01/01/4000* mueve_h 29/06/2001 30/06/2001 carga_s 29/06/2001 30/06/2001 carga_v 29/06/2001 30/06/2001 carga_h 29/06/2001 30/06/2001 mueve_h 29/06/2001 30/06/2001 carga_v_fl29/06/2001 06/07/2001 carga_v_fl 02/07/2001 What is the sintax to execute the procedures, some every day, some every week and some two times a week, passing different dates automatically as a parameter. Thanks, Ramon Estevez -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Raymond Lee Meng Hong INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: DBMS_JOB
Query user_jobs view. It will give you the procedure name, last date time when it was executed and the next date time when it will get executed next. Prakash -Original Message- Sent: Friday, June 29, 2001 5:02 PM To: Multiple recipients of list ORACLE-L Hi List, How do I know what procedures had been submitted thru the DBMS_JOB package, and would be the next execution. Ramón Estévez *809-565-3121 x 225 * [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, Prakash INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB
Thanks Ray and Dick, This the output, obviously some mistakes(*), how do I stop them from executing, (take them out) LAST_DATE NEXT_DATE -- -- carga_d 29/06/2001 30/06/2001 mueve_h_f 20/02/2001 01/01/4000* carga_d 29/06/2001 30/06/2001 carga_v_fl29/06/2001 06/07/2001 carga_v_fl14/03/2001 01/01/4000* carga_v_f_s 29/06/2001 06/07/2001 carga_v_f_m 05/06/2001 05/07/2001 carga_d 20/03/2001 01/01/4000* carga_d 19/03/2001 01/01/4000* carga_s 20/03/2001 01/01/4000* carga_v 20/03/2001 01/01/4000* mueve_h 29/06/2001 30/06/2001 carga_s 29/06/2001 30/06/2001 carga_v 29/06/2001 30/06/2001 carga_h 29/06/2001 30/06/2001 mueve_h 29/06/2001 30/06/2001 carga_v_fl29/06/2001 06/07/2001 carga_v_fl 02/07/2001 What is the sintax to execute the procedures, some every day, some every week and some two times a week, passing different dates automatically as a parameter. Thanks, Ramon Estevez -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_JOB
Here's a script I use to view jobs: set linesize 132 select to_char(job,'FM990') job, substr(schema_user,1,6) owner, substr(what,1,20) procedure, to_char(last_date,'MM/DD/YY HH24:MI') LAST RUN, decode(this_date,null, ' '||to_char(next_date,'MM/DD/YY HH24:MI'), '*'||to_char(this_date,'MM/DD/YY HH24:MI')) *RUNNING/NEXT, rpad(Broken,4) Brkn, rpad(to_char(failures,'FM90'),4) fail, substr(interval,1,30) interval from dba_jobs / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike J Kurth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_job query?
we had these situations for this we had to break the job first and then kill the snp process after some time u will see the sid out of v$session. Even if you kill the snp it will be kicked back later on from pid or spid i think i am not sure but it works Try this out it should work Well there must be some better answers from the experts out here check it out From: Seema Singh [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: dbms_job query? Date: Fri, 01 Jun 2001 14:11:10 -0800 Hi If status=KILLED in v$session and the process is SNP oracle background process corresponding to that sid and dba_jobs_running has the same sid,no entry corresponding in dba_jobs then how to kill that process. Thx -seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ARUN K C INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
