RE: dbms_job issue.

2003-09-19 Thread Hatzistavrou John 









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

2003-09-18 Thread Stephane Faroult 
> [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 issue.

2003-09-18 Thread Avnish.Rastogi 



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

2003-09-18 Thread Jamadagni, Rajendra 



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

2003-09-18 Thread Avnish.Rastogi 



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

2003-09-18 Thread Igor Neyman 









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

2003-09-18 Thread Venu Gopal 









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

2003-07-25 Thread bulbultyagi 
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

2003-07-25 Thread bulbultyagi 
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

2003-07-25 Thread Jamadagni, Rajendra 
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

2003-07-25 Thread Pete Finnigan 
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

2003-07-25 Thread Sarnowski, Chris 

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 scheduling

2003-07-23 Thread Jamadagni, Rajendra 
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 scheduling

2003-07-23 Thread Jamadagni, Rajendra 
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 
  separa

RE: DBMS_JOB scheduling

2003-07-23 Thread Garry Gillies 
> 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

2003-07-23 Thread Igor Neyman 
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

RE: DBMS_JOB scheduling

2003-07-23 Thread Jamadagni, Rajendra 
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 t

Re: dbms_job

2003-02-25 Thread Connor McDonald 
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

2003-02-20 Thread David . Schmoldt 
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

2003-02-20 Thread Jamadagni, Rajendra 
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=>working now

2003-02-20 Thread CHAN Chor Ling Catherine (CSC) 
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

RE: DBMS_JOB : Can submit but does not execute

2003-02-19 Thread Jesse, Rich 
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 : Can submit but does not execute

2003-02-19 Thread Jamadagni, Rajendra 
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    -- Mailin

RE: DBMS_JOB : Can submit but does not execute

2003-02-19 Thread Jay Hostetter 
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 hav

RE: DBMS_JOB : Can submit but does not execute

2003-02-19 Thread Rajesh Dayal 
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

2003-02-19 Thread Michal Zaschke 
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

2003-02-19 Thread Ramon E. Estevez 
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

2003-02-18 Thread CHAN Chor Ling Catherine (CSC) 
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: DBMS_JOB

2003-02-18 Thread Mark Leith 
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 remo

RE: DBMS_JOB

2003-02-18 Thread Deshpande, Kirti 
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

2003-02-18 Thread Mark Leith 
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

2003-02-18 Thread Pat Hildebrand 
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

2003-02-18 Thread Jay Hostetter 
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 
D&E 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 - running jobs every 15 minutes

2003-01-22 Thread Jared . Still 
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

2003-01-22 Thread Vladimir Begun 
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

2003-01-22 Thread Ron Rogers 
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 ...  

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- 
 ] 
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

2003-01-22 Thread Freeman Robert - IL 
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

2003-01-22 Thread Gene Sais 



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 of jobs will increase and compete 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 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 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

2003-01-22 Thread Jesse, Rich 
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

2003-01-22 Thread Jesse, Rich 
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

2003-01-22 Thread Vladimir Begun 
[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

2003-01-22 Thread Freeman Robert - IL 
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: RE: dbms_job - running jobs every 15 minutes

2003-01-22 Thread Jared . Still 
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: [E

RE: dbms_job - running jobs every 15 minutes

2003-01-22 Thread Jared . Still 
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: dbms_job - running jobs every 15 minutes

2003-01-22 Thread Jared . Still 
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 servic

RE: RE: dbms_job - running jobs every 15 minutes

2003-01-22 Thread Freeman Robert - IL 
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

2003-01-22 Thread Vladimir Begun 
[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: dbms_job - running jobs every 15 minutes

2003-01-22 Thread Freeman Robert - IL 
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

2003-01-22 Thread Freeman Robert - IL 
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 PROTECTE

RE: dbms_job - running jobs every 15 minutes

2003-01-22 Thread Freeman Robert - IL 
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 ...  
  
  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

2003-01-22 Thread Koivu, Lisa 
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 ...  

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

2003-01-22 Thread Jamadagni, Rajendra 
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

2003-01-22 Thread Freeman Robert - IL 
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 ...  

  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

2003-01-22 Thread Arup Nanda 
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

2003-01-22 Thread Jamadagni, Rajendra 
Title: RE: dbms_job - running jobs every 15 minutes





I simplified it by using cron instead ... 


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

2003-01-22 Thread Tim Gorman 
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: RE: dbms_job - running jobs every 15 minutes

2003-01-22 Thread Stephane Faroult 
>
>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

2003-01-21 Thread Robert Freeman 
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 - running jobs every 15 minutes

2003-01-21 Thread Robert Freeman 
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

2003-01-21 Thread Vladimir Begun 
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 interval 10till

2003-01-10 Thread Khedr, Waleed 
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 EX

Re: dbms_job interval 10till

2003-01-10 Thread Barbara Baker 
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

2003-01-10 Thread Stephane Faroult 
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 problem

2002-12-13 Thread JRicard982 
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

2002-07-18 Thread Jesse, Rich 

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: RE: DBMS_JOB interval wierdness

2002-07-16 Thread Jesse, Rich 

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 interval wierdness

2002-07-16 Thread kkennedy 

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: DBMS_JOB interval wierdness

2002-07-16 Thread Jesse, Rich 

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

2002-07-15 Thread David Marshall 

I assume you have job_queue_processes = n  # where n > 0
After submitting the job you did issue a commit in that session?


-Original Message-
Sent: 15 July 2002 16:28
To: Multiple recipients of list ORACLE-L


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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: David Marshall
  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

2002-07-15 Thread Jamadagni, Rajendra 

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

2002-07-15 Thread mitchell 

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 - SOLVED !

2002-07-14 Thread Andrey Bronfin 

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 does not run on NT

2002-07-14 Thread CP 

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

2002-07-14 Thread Jack Silvey 

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 question

2002-05-29 Thread Stephane Faroult 


>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

2002-04-19 Thread Khedr, Waleed 

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

RE: Dbms_job not running

2002-04-19 Thread Conboy, Jim 

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

2002-04-19 Thread Reddy, Madhusudana 

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

2002-04-19 Thread Igor Neyman 

Did you set JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL in your
"init.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: RE: DBMS_JOB statement

2001-09-12 Thread Thomas, Kevin 

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

Ang: RE: DBMS_JOB statement

2001-09-12 Thread Roland . Skoldblom 


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: DBMS_JOB statement

2001-09-11 Thread Thomas, Kevin 

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

2001-09-06 Thread Arich Henneman 

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

2001-09-05 Thread Arich Henneman 

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

2001-09-05 Thread Arich Henneman 

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

2001-08-29 Thread Guy Hammond 

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

2001-08-28 Thread Connor McDonald 

and DBA_JOBS_RUNNING

 --- Ramon Estevez <[EMAIL PROTECTED]>
wrote: > Use the DBA_JOBS view
> 
> Ramon E. Estevez
> [EMAIL PROTECTED]
>  
> 809-565-3121
> 
> 
> -Mensaje original-
> De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En
> nombre de Rangachari
> Sundar
> Enviado el: Tuesday, 28 August, 2001 9:36 AM
> Para: Multiple recipients of list ORACLE-L
> Asunto: DBMS_JOB Help
> 
> 
> Dear All,
> 
> Can anyone tell me how to find out currently running
> jobs initiated thru
> DBMS_JOB package?
> 
> Thanks
> 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). 

=
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 Help

2001-08-28 Thread Ramon Estevez 

Use the DBA_JOBS view

Ramon E. Estevez
[EMAIL PROTECTED]  
809-565-3121


-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Rangachari
Sundar
Enviado el: Tuesday, 28 August, 2001 9:36 AM
Para: Multiple recipients of list ORACLE-L
Asunto: DBMS_JOB Help


Dear All,

Can anyone tell me how to find out currently running jobs initiated thru
DBMS_JOB package?

Thanks
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 Help

2001-08-28 Thread Ramon Estevez 

Hola,

You can use dbms_job.remove(jobnumber)

Suerte !!

Ramon E. Estevez
[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 Help

2001-08-27 Thread Paul Drake 

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

2001-08-27 Thread Connor McDonald 

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

2001-08-27 Thread Jon Walthour 

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

2001-07-09 Thread Mitchell 

 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 o

RE: DBMS_JOB

2001-07-09 Thread nlzanen1 


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
alge

RE: DBMS_JOB

2001-07-06 Thread Yadav, Shailesh 

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

2001-07-02 Thread Ramon Estevez 

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

RE: DBMS_JOB

2001-07-02 Thread Ramon Estevez 

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 b

RE: DBMS_JOB

2001-07-02 Thread A. Bardeen 

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

2001-07-01 Thread Raymond Lee Meng Hong 

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

2001-06-29 Thread Mike J Kurth 


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

  1   2   >