Help needed -- Replication and DBMS_JOB
Dear Gurus, I have dbms_job in replication environment to push deffered transactions from site A to B and B to A. The job which is running at site A is working fine but job which is running at site B is not pushing the transactions automatically. But if I do it manually (either dbms_job.run(job_number) or using OEM UI) it is working fine. Otherwise dbms_job failed count keep increases every minute and finally it becomes broken. The owner of the DBMS_JOB is REPADMIN at both sites. Is there a way to check the root cause for the failed DBMS_JOB?. I need to check the reason why the particular DBMS_JOB is failing. I don't want DBA_JOBs view because DBA_JOBS gives the number of failed count. Note:- Number of Job_queue_processes is configured as 20 in both sites(A and B). Also iI confirmed that 20 # of snp unix process is running at each node. job_queue_interval is 60 secs. thanks Jay -- 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: Help needed -- Replication and DBMS_JOB
Jay, Check http://www.oriole.com/aunt_2001_0.html and look for the 19th. March 2001 entry. Otherwise look for a snp*.trc in either bdump or udump (never remember where it goes). HTH SF - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 14 Nov 2003 05:29:30 Dear Gurus, I have dbms_job in replication environment to push deffered transactions from site A to B and B to A. The job which is running at site A is working fine but job which is running at site B is not pushing the transactions automatically. But if I do it manually (either dbms_job.run(job_number) or using OEM UI) it is working fine. Otherwise dbms_job failed count keep increases every minute and finally it becomes broken. The owner of the DBMS_JOB is REPADMIN at both sites. Is there a way to check the root cause for the failed DBMS_JOB?. I need to check the reason why the particular DBMS_JOB is failing. I don't want DBA_JOBs view because DBA_JOBS gives the number of failed count. Note:- Number of Job_queue_processes is configured as 20 in both sites(A and B). Also iI confirmed that 20 # of snp unix process is running at each node. job_queue_interval is 60 secs. thanks Jay -- 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: RE: Help needed -- Replication and DBMS_JOB
Stephane, This is what I was exactly looking for. Thank you so much. - Original Message - Date: Friday, November 14, 2003 8:59 am Jay, Check http://www.oriole.com/aunt_2001_0.html and look for the 19th. March 2001 entry. Otherwise look for a snp*.trc in either bdump or udump (never remember where it goes). HTH SF - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 14 Nov 2003 05:29:30 Dear Gurus, I have dbms_job in replication environment to push deffered transactions from site A to B and B to A. The job which is running at site A is working fine but job which is running at site B is not pushing the transactions automatically. But if I do it manually (either dbms_job.run(job_number) or using OEM UI) it is working fine. Otherwise dbms_job failed count keep increases every minute and finally it becomes broken. The owner of the DBMS_JOB is REPADMIN at both sites. Is there a way to check the root cause for the failed DBMS_JOB?. I need to check the reason why the particular DBMS_JOB is failing. I don't want DBA_JOBs view because DBA_JOBS gives the number of failed count. Note:- Number of Job_queue_processes is configured as 20 in both sites(A and B). Also iI confirmed that 20 # of snp unix process is running at each node. job_queue_interval is 60 secs. thanks Jay -- 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). -- 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).
stupid dbms_job question
im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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: Re: stupid dbms_job question
id prefer to handle this in the database. From: Gene Sais [EMAIL PROTECTED] Date: 2003/10/23 Thu AM 10:29:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: stupid dbms_job question It's called cron :). Or you could run a shell script that executes then sleeps for 5 mins. [EMAIL PROTECTED] 10/23/03 10:09AM im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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). It's called cron :). Or you could run a shell script that executes then sleeps for 5 mins. [EMAIL PROTECTED] 10/23/03 10:09AM im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows...variable jobno number;variable instno number;begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit;end;i then do:dbms_job.interval(:jobno,'trunc(sysdate+1/96)';commit;my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run.Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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.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: stupid dbms_job question
I had an issue with dmbs_job where the jobs would become broken if I altered them. Have you checked if the job becomes broken after you change the interval? Gudmundur im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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). Þessi póstur var sendur með vefpósti mi, http://www.mi.is -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Josepsson 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: stupid dbms_job question
Just a shot in the dark Have you set initialization parameters in the init file? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 6:14 PM To: Multiple recipients of list ORACLE-L Subject: Re: Re: stupid dbms_job question id prefer to handle this in the database. From: Gene Sais [EMAIL PROTECTED] Date: 2003/10/23 Thu AM 10:29:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: stupid dbms_job question It's called cron :). Or you could run a shell script that executes then sleeps for 5 mins. [EMAIL PROTECTED] 10/23/03 10:09AM im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gints Plivna 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: stupid dbms_job question
An interval of trunc(sysdate)+(trunc(to_char(sysdate,'s')/900)+1)*5/24/60 ought to give you every 5 minutes. Courtesy of Tom... On Thu, 2003-10-23 at 10:29, Gene Sais wrote: It's called cron :). Or you could run a shell script that executes then sleeps for 5 mins. [EMAIL PROTECTED] 10/23/03 10:09AM im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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). Richard Quintin, DBA Information Systems Computing, DBMS Virginia Tech -- Magnificent promises are always to be suspected. -- Theodore Parker -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Quintin, Richard 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: stupid dbms_job question
what initialization parameters? job_queue_processes is fine. From: Gints Plivna [EMAIL PROTECTED] Date: 2003/10/23 Thu AM 11:49:32 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Re: stupid dbms_job question Just a shot in the dark Have you set initialization parameters in the init file? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 6:14 PM To: Multiple recipients of list ORACLE-L Subject: Re: Re: stupid dbms_job question id prefer to handle this in the database. From: Gene Sais [EMAIL PROTECTED] Date: 2003/10/23 Thu AM 10:29:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: stupid dbms_job question It's called cron :). Or you could run a shell script that executes then sleeps for 5 mins. [EMAIL PROTECTED] 10/23/03 10:09AM im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gints Plivna 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: Re: stupid dbms_job question
Ryan, remove the trunc() from sysdate+1/96 trunc is removing your time part. or you should be using ROUND(SYSDATE+1/96,'MI') instead. 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 ! ** 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. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: stupid dbms_job question
What is JOB_QUEUE_INTERVAL set to? If it's more than 5 minutes then what your seeing is what you get. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, October 23, 2003 10:09 AM To: Multiple recipients of list ORACLE-L im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick 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: stupid dbms_job question
JOB_QUEUE_INTERVAL I think But I found that it is obsolete in Release 9.0.1 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 7:15 PM To: Multiple recipients of list ORACLE-L Subject: RE: Re: stupid dbms_job question what initialization parameters? job_queue_processes is fine. From: Gints Plivna [EMAIL PROTECTED] Date: 2003/10/23 Thu AM 11:49:32 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Re: stupid dbms_job question Just a shot in the dark Have you set initialization parameters in the init file? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 6:14 PM To: Multiple recipients of list ORACLE-L Subject: Re: Re: stupid dbms_job question id prefer to handle this in the database. From: Gene Sais [EMAIL PROTECTED] Date: 2003/10/23 Thu AM 10:29:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: stupid dbms_job question It's called cron :). Or you could run a shell script that executes then sleeps for 5 mins. [EMAIL PROTECTED] 10/23/03 10:09AM im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gints Plivna 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gints Plivna 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: stupid dbms_job question
Check if the job fails for some reason, any trace files for j??? in udump or bdump Also check job_queue_processes setting Waleed -Original Message- Sent: Thursday, October 23, 2003 11:14 AM To: Multiple recipients of list ORACLE-L id prefer to handle this in the database. From: Gene Sais [EMAIL PROTECTED] Date: 2003/10/23 Thu AM 10:29:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: stupid dbms_job question It's called cron :). Or you could run a shell script that executes then sleeps for 5 mins. [EMAIL PROTECTED] 10/23/03 10:09AM im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: stupid dbms_job question
I am so sorry, I replied to the wrong email. -Original Message- Sent: Thursday, October 23, 2003 12:46 PM To: '[EMAIL PROTECTED]' Are you heading out to lunch -Original Message- Sent: Thursday, October 23, 2003 11:14 AM To: Multiple recipients of list ORACLE-L id prefer to handle this in the database. From: Gene Sais [EMAIL PROTECTED] Date: 2003/10/23 Thu AM 10:29:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: stupid dbms_job question It's called cron :). Or you could run a shell script that executes then sleeps for 5 mins. [EMAIL PROTECTED] 10/23/03 10:09AM im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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). --- This message and any included attachments are from Siemens Medical Solutions USA, Inc. and are intended only for the addressee(s). The information contained herein may include trade secrets or privileged or otherwise confidential information. Unauthorized review, forwarding, printing, copying, distributing, or using such information is strictly prohibited and may be unlawful. If you received this message in error, or have reason to believe you are not authorized to receive it, please promptly delete this message and notify the sender by e-mail with a copy to [EMAIL PROTECTED] Thank you -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cortese Joseph 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: stupid dbms_job question
Are you heading out to lunch -Original Message- Sent: Thursday, October 23, 2003 11:14 AM To: Multiple recipients of list ORACLE-L id prefer to handle this in the database. From: Gene Sais [EMAIL PROTECTED] Date: 2003/10/23 Thu AM 10:29:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: stupid dbms_job question It's called cron :). Or you could run a shell script that executes then sleeps for 5 mins. [EMAIL PROTECTED] 10/23/03 10:09AM im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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). --- This message and any included attachments are from Siemens Medical Solutions USA, Inc. and are intended only for the addressee(s). The information contained herein may include trade secrets or privileged or otherwise confidential information. Unauthorized review, forwarding, printing, copying, distributing, or using such information is strictly prohibited and may be unlawful. If you received this message in error, or have reason to believe you are not authorized to receive it, please promptly delete this message and notify the sender by e-mail with a copy to [EMAIL PROTECTED] Thank you -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cortese Joseph 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: stupid dbms_job question
Well, you didn't say what version of the database we were talking about. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, October 23, 2003 12:39 PM To: Multiple recipients of list ORACLE-L JOB_QUEUE_INTERVAL I think But I found that it is obsolete in Release 9.0.1 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 7:15 PM To: Multiple recipients of list ORACLE-L Subject: RE: Re: stupid dbms_job question what initialization parameters? job_queue_processes is fine. From: Gints Plivna [EMAIL PROTECTED] Date: 2003/10/23 Thu AM 11:49:32 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Re: stupid dbms_job question Just a shot in the dark Have you set initialization parameters in the init file? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 6:14 PM To: Multiple recipients of list ORACLE-L Subject: Re: Re: stupid dbms_job question id prefer to handle this in the database. From: Gene Sais [EMAIL PROTECTED] Date: 2003/10/23 Thu AM 10:29:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: stupid dbms_job question It's called cron :). Or you could run a shell script that executes then sleeps for 5 mins. [EMAIL PROTECTED] 10/23/03 10:09AM im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gints Plivna 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gints Plivna 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
intermedia text and dbms_job problem
OK, I know I must be doing something wrong, would somebody please point out what it is? I would like to set up a job to sync an intermedia text index. Here is my job: SQL select what from all_jobs where job = 2; WHAT - ctx_ddl.sync_index ( 'ctx_xml_text' ); I can execute the procedute from the command line like so: SQL execute ctx_ddl.sync_index ( 'ctx_xml_text' ); PL/SQL procedure successfully completed. But the job itself fails: SQL execute dbms_job.run ( 2 ); BEGIN dbms_job.run ( 2 ); END; * ERROR at line 1: ORA-12011: execution of 1 jobs failed ORA-06512: at SYS.DBMS_IJOB, line 405 ORA-06512: at SYS.DBMS_JOB, line 267 ORA-06512: at line 1 in my init.ora I have: job_queue_processes = 4 job_queue_interval = 60 I am in the ctxapp role, and ctxsys has granted me execute on ctx_ddl. The only other job works just fine. OS=Solaris, Version=8.1.7 What the heck is going on? Thanks in advance, Bill Bill Tantzen University of Minnesota Libraries [EMAIL PROTECTED] 612-626-9949 (office) 612-250-6125 (cell) I guess the man's a genius, but what a dirty mind he has, hasn't he? -- Nora Joyce -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Tantzen 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: intermedia text and dbms_job problem
bill this is a bug..infact it was happening in 8i and oracle said they will try to fix in 9i. u have to call the procedure in the ur job and embed the sync index in ur procedure. here is a snippet.u can try this way. create or replace procedure i_doc_sync is begin ctx_ddl.sync_index( idx_name = 'prb_title'); end; declare v_job number; begin dbms_job.submit( job=v_job, what='i_doc_sync;', next_date=sysdate, interval='sysdate+1/24'); end; that should work saiBill Tantzen [EMAIL PROTECTED] wrote: OK, I know I must be doing something wrong, would somebody please pointout what it is? I would like to set up a job to sync an intermedia textindex. Here is my job:SQL select what from all_jobs where job = 2;WHAT-ctx_ddl.sync_index ( 'ctx_xml_text' );I can execute the procedute from the command line like so:SQL execute ctx_ddl.sync_index ( 'ctx_xml_text' );PL/SQL procedure successfully completed.But the job itself fails:SQL execute dbms_job.run ( 2 );BEGIN dbms_job.run ( 2 ); END;*ERROR at line 1:ORA-12011: execution of 1 jobs failedORA-06512: at "SYS.DBMS_IJOB", line 405ORA-06512: at "SYS.DBMS_JOB", line 267ORA-06512: at line 1in my init.ora I have:job_queue_processes = 4job_queue_interval = 60! I am in the ctxapp role, and ctxsys has granted me execute on ctx_ddl.The only other job works just fine.OS=Solaris, Version=8.1.7What the heck is going on?Thanks in advance,BillBill Tantzen University of Minnesota Libraries[EMAIL PROTECTED]612-626-9949 (office) 612-250-6125 (cell)I guess the man's a genius, but whata dirty mind he has, hasn't he? -- Nora Joyce-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Bill TantzenINET: [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: intermedia text and dbms_job problem
Title: Message Sai, Thank you so very much! It worked perfectly!!! Bill Bill TantzenUniversity of Minnesota Libraries[EMAIL PROTECTED]612-626-9949 (office) 612-250-6125 (cell)I guess the man's a genius, but whata dirty mind he has, hasn't he? -- Nora Joyce -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sai SelvaganesanSent: Wednesday, October 08, 2003 3:19 PMTo: Multiple recipients of list ORACLE-LSubject: Re: intermedia text and dbms_job problem bill this is a bug..infact it was happening in 8i and oracle said they will try to fix in 9i. u have to call the procedure in the ur job and embed the sync index in ur procedure. here is a snippet.u can try this way. create or replace procedure i_doc_sync is begin ctx_ddl.sync_index( idx_name = 'prb_title'); end; declare v_job number; begin dbms_job.submit( job=v_job, what='i_doc_sync;', next_date=sysdate, interval='sysdate+1/24'); end; that should work sai
RE: dbms_job issue.
Have you checked number of queues lately? Kind Regards, Hatzistavrou Yannis Database Administrator SchlumbergerSema Phone ext. 478 Email: [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 8:30 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME -- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER: This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
dbms_job issue.
Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME-- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
RE: dbms_job issue.
Re-submit the jobthat should work It has a long theory!! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:00 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME -- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER: This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
RE: dbms_job issue.
You said for the last 3 days, so what do you do to get it running again? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 12:30 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME -- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER: This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
RE: dbms_job issue.
Already tried to resubmit it twice, everytime its breaking around mid night. -Original Message-From: Venu Gopal [mailto:[EMAIL PROTECTED]Sent: Thursday, September 18, 2003 10:40 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job issue. Re-submit the jobthat should work It has a long theory!! -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]Sent: Thursday, September 18, 2003 11:00 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME-- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *** DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
RE: dbms_job issue.
IIRC it is a bug, on 5L oracle sometimes _forgets_ to run the job. We noticed this in early versions of 9i. We stopped using it and we use cron instead. If you want to test this, set job_queue_processes to say 20 and see, it will probably work. The workaround from oracle states to set it to 'really high' number. I don't remember the bug# right now ... -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Thursday, September 18, 2003 2:00 PMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job issue. Already tried to resubmit it twice, everytime its breaking around mid night. -Original Message-From: Venu Gopal [mailto:[EMAIL PROTECTED]Sent: Thursday, September 18, 2003 10:40 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job issue. Re-submit the job...that should work... It has a long theory...!! -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]Sent: Thursday, September 18, 2003 11:00 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME-- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *** DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: dbms_job issue.
Resubmit it and it runs fine until mid night. So I really dont have any stats for this week. -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]Sent: Thursday, September 18, 2003 10:45 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job issue. You said for the last 3 days, so what do you do to get it running again? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]Sent: Thursday, September 18, 2003 12:30 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I amnot seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME-- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
Re: dbms_job issue.
[EMAIL PROTECTED] wrote: Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I am not seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME -- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0175 The big nuisance with jobs is that they leave you in the dark when they fail. You may find some of the stuff here interesting to diagnose : http://www.oriole.com/aunt_2001_0.html (March letter) -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
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. .. -- 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
It would seem that dbms_system has become undocumented. It used to be partially documented, e.g. there was a description of set_sql_trace_in_session, but they seem to have pulled that as of 8.1. You're supposed to use dbms_support now, but that too seems to be undocumented. At least on Solaris, you have to beg support for the 8i install script, but the 9i install script is included in $ORACLE_HOME/rdbms/admin. There are some notes about this in some of the forums at metalink ( I searched 'dbms_system dbms_support'). HTH, -Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job Hello list, I am running 9.2.0.1.0 enterprise edition on win32. Why doesn't the Supplied PLSQL Packages and Types Reference docs mention dbms_system ? It doesn't seem to be explained anywhere in the docs although the admin docs say that it can be used enable sql_trace for another session. I was able to do that only after learning about its parameters thru : sql describe dbms_system while I was logged in as sysdba. .. LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: dbms_job
Hi The package dbms_system is now private, the body and header are both created in $ORACLE_HOME/rdbms/admin/prvtutil.plb. The sql file $ORACLE_HOME/rdbms/admin/dbmsutil.sql has the following in it now: Rem dbms_system - database system level commands (moved to Rem prvtutil.sql for more obscurity) kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dbms_job
Title: RE: dbms_job www.orafaq.com/papers/dbms_sys.doc Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Pete Finnigan [mailto:[EMAIL PROTECTED]] Sent: Friday, July 25, 2003 5:14 PM To: Multiple recipients of list ORACLE-L Subject: Re: dbms_job Hi The package dbms_system is now private, the body and header are both created in $ORACLE_HOME/rdbms/admin/prvtutil.plb. The sql file $ORACLE_HOME/rdbms/admin/dbmsutil.sql has the following in it now: Rem dbms_system - database system level commands (moved to Rem prvtutil.sql for more obscurity) kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: dbms_job
Thanks pete - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, July 26, 2003 02:44 Hi The package dbms_system is now private, the body and header are both created in $ORACLE_HOME/rdbms/admin/prvtutil.plb. The sql file $ORACLE_HOME/rdbms/admin/dbmsutil.sql has the following in it now: Rem dbms_system - database system level commands (moved to Rem prvtutil.sql for more obscurity) kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: dbms_job
Thanks chris. Let me know if you find out anything more. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, July 25, 2003 23:39 It would seem that dbms_system has become undocumented. It used to be partially documented, e.g. there was a description of set_sql_trace_in_session, but they seem to have pulled that as of 8.1. You're supposed to use dbms_support now, but that too seems to be undocumented. At least on Solaris, you have to beg support for the 8i install script, but the 9i install script is included in $ORACLE_HOME/rdbms/admin. There are some notes about this in some of the forums at metalink ( I searched 'dbms_system dbms_support'). HTH, -Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job Hello list, I am running 9.2.0.1.0 enterprise edition on win32. Why doesn't the Supplied PLSQL Packages and Types Reference docs mention dbms_system ? It doesn't seem to be explained anywhere in the docs although the admin docs say that it can be used enable sql_trace for another session. I was able to do that only after learning about its parameters thru : sql describe dbms_system while I was logged in as sysdba. .. LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
cron.next_date - was DBMS_JOB scheduling
Package can be obtained from http://oracledba.pwp.blueyonder.co.uk/cron.html Use at your own risk. Garry Gillies 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).
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] 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
RE: DBMS_JOB scheduling
Title: RE: DBMS_JOB scheduling Garry, 1. have you tried select to_char(sysdate,'D') from dual ?? This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number. nevertheless, I think what you have attempted is fantastic and worthy of adoption ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Garry Gillies [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 23, 2003 5:59 AM To: Multiple recipients of list ORACLE-L Subject: DBMS_JOB scheduling Any Interest? The DBMS_JOB package is supplied by Oracle to allow the running of procedures at regular intervals. Unfortunately the INTERVAL parameter is limited to 128 characters, which prevents you from getting very complex (user defined functions [in the interval parameter] do not work well - according to Fuerstein in his book Oracle Built In Packages). The situation is eased somewhat by the fact that the NEXT_DATE parameter can be supplied to the procedure as an in/out parameter - and the procedure can contain whatever code is necessary to calculate when next to run. This is all very well, but custom coding scheduling routines can quickly become tedious. On the basis of do it once and get it over with I have written a function called NEXT_DATE which I have wrapped in a package called CRON. There is a Unix program called cron which runs jobs on a regular basis. Although the scheduling data supplied to cron is simple and concise, complex schedules are easy to specify. The NEXT_DATE function takes in a cron schedule string and returns the next date that conforms to the schedule - or you can supply a cron schedule and a date and it will return the first date after the supplied date that conforms to the schedule. At the moment it is not very friendly on the error detection front. A VALUE_ERROR is returned if it deems the cron schedule to be invalid. You will also get a VALUE_ERROR if the next valid date is more than twenty seven years in the future. DBMS_OUTPUT is used to display error messages which will hopefully give you a clue. This will be improved if I receive enough complaints ( and suggestions for improvements). THE CRON SCHEDULE A cron schedule consists of five components, each separated from the next by a space. The syntax is identical for all components. The components represent Minute in Hour Hour in day Day in month Month in year Day of Week - A bit of a bugger this one. In Unix land the day numbering runs from 0-6 with 0 being Sunday. In Oracle the day numbering depends on the setting of NLS_TERRITORY. I have chosen to go with ISO standard 8601:1998 which runs from 1-7 with 1 being Monday. This is so close to the Unix convention that I can interpret Unix cron schedules correctly. Curiously, Oracle do not provide a date format which supplies this number. The ISO week number is available with the format 'IW', but not the ISO day number. If you have a field of type date called dt, you can obtain the ISO day number with ( trunc(dt) - trunc(dt ,'IW') ) + 1 A component can consist of an asterisk * which represents all valid values or a number of elements separated by a comma (if only one element is supplied, forget the comma). An element can be a single number - valid for the component (32 in Day in month is invalid) or two numbers separated by a hyphen - which represents a range. EXAMPLES Run every hour on the hour 0 * * * * Run twice every hour, on the hour and on the half hour 0,30 * * * * Run twice every hour, on the hour and on the half hour between 08:00 and 16:59 0,30 8-16 * * * Run twice every hour, on the hour and on the half hour between 08:00 and 16:59, Monday to Friday 0,30 8-16 * * 1-5 Run at 11:12 every Friday the 13th 11 12 13 * 5 Run at 04:00 every leap year on february 29 0 4 29 2 * Run at 04:00 every leap year on february 29 when february 29 is a Thursday 0 4 29 2 4 Garry Gillies Database Administrator Business Systems Weir Pumps Ltd 149 Newlands Road, Cathcart, Glasgow, G44 4EX T: +44 0141 308 3982 F: +44 0141 633 1147 E: [EMAIL PROTECTED] *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you
RE: DBMS_JOB scheduling
Title: RE: DBMS_JOB scheduling Raj, You must be speaking from UNIX heights -J Under Windows I find dbms_job much more reliable than windows at scheduling. Actually, never had problems with dbms_job forgetting to run a job. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, Rajendra Sent: Wednesday, July 23, 2003 9:24 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB scheduling Garry, 1. have you tried select to_char(sysdate,'D') from dual ?? This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number. nevertheless, I think what you have attempted is fantastic and worthy of adoption ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Garry Gillies [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 23, 2003 5:59 AM To: Multiple recipients of list ORACLE-L Subject: DBMS_JOB scheduling Any Interest? The DBMS_JOB package is supplied by Oracle to allow the running of procedures at regular intervals. Unfortunately the INTERVAL parameter is limited to 128 characters, which prevents you from getting very complex (user defined functions [in the interval parameter] do not work well - according to Fuerstein in his book Oracle Built In Packages). The situation is eased somewhat by the fact that the NEXT_DATE parameter can be supplied to the procedure as an in/out parameter - and the procedure can contain whatever code is necessary to calculate when next to run. This is all very well, but custom coding scheduling routines can quickly become tedious. On the basis of do it once and get it over with I have written a function called NEXT_DATE which I have wrapped in a package called CRON. There is a Unix program called cron which runs jobs on a regular basis. Although the scheduling data supplied to cron is simple and concise, complex schedules are easy to specify. The NEXT_DATE function takes in a cron schedule string and returns the next date that conforms to the schedule - or you can supply a cron schedule and a date and it will return the first date after the supplied date that conforms to the schedule. At the moment it is not very friendly on the error detection front. A VALUE_ERROR is returned if it deems the cron schedule to be invalid. You will also get a VALUE_ERROR if the next valid date is more than twenty seven years in the future. DBMS_OUTPUT is used to display error messages which will hopefully give you a clue. This will be improved if I receive enough complaints ( and suggestions for improvements). THE CRON SCHEDULE A cron schedule consists of five components, each separated from the next by a space. The syntax is identical for all components. The components represent Minute in Hour Hour in day Day in month Month in year Day of Week - A bit of a bugger this one. In Unix land the day numbering runs from 0-6 with 0 being Sunday. In Oracle the day numbering depends on the setting of NLS_TERRITORY. I have chosen to go with ISO standard 8601:1998 which runs from 1-7 with 1 being Monday. This is so close to the Unix convention that I can interpret Unix cron schedules correctly. Curiously, Oracle do not provide a date format which supplies this number. The ISO week number is available with the format 'IW', but not the ISO day number. If you have a field of type date called dt, you can obtain the ISO day number with ( trunc(dt) - trunc(dt ,'IW') ) + 1 A component can consist of an asterisk * which represents all valid values or a number of elements separated by a comma (if only one element is supplied, forget the comma). An element can be a single number - valid for the component (32 in Day in month is invalid) or two numbers separated by a hyphen - which represents a range. EXAMPLES Run every hour on the hour 0 * * * * Run twice every hour, on the hour and on the half hour 0,30 * * * * Run twice every hour, on the hour and on the half hour between 08:00 and 16:59 0,30 8-16 * * * Run twice every hour, on the hour and on the half hour between 08:00 and 16:59, Monday to Friday 0,30 8-16 * * 1-5 Run at 11:12 every Friday the 13th 11 12 13 * 5 Run at 04:00 every leap year on february 29 0 4 29 2 * Run at 04:00 every leap year on february 29 when february 29 is a Thursday 0 4 29 2 4 Garry Gillies Database Administrator Business Systems Weir Pumps Ltd 149 Newlands Road, Cathcart, Glasgow, G44 4EX T: +44 0141 308 3982 F: +44 0141 633 1147 E: [EMAIL
RE: DBMS_JOB scheduling
1. have you tried select to_char(sysdate,'D') from dual ?? Yes, and it works fine on MY servers, but the docs say that the value returned depends on your NLS_TERRITORY setting. I am trying to get it to work for everybody. This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number. I am sorry to hear that. We are on 8.1.7 and have been using since 7.3 with no major problems. nevertheless, I think what you have attempted is fantastic and worthy of adoption ... Are you looking for money? :-) Garry Gillies Database Administrator Business Systems Weir Pumps Ltd 149 Newlands Road, Cathcart, Glasgow, G44 4EX T: +44 0141 308 3982 F: +44 0141 633 1147 E: [EMAIL PROTECTED] Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 23/07/03 15:24 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: DBMS_JOB scheduling Garry, 1. have you tried select to_char(sysdate,'D') from dual ?? This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number. nevertheless, I think what you have attempted is fantastic and worthy of adoption ... CONFIDENTIAL: The information contained in this email (including any attachments) is confidential, subject to copyright and for the use of the intended recipient only. If you are not the intended recipient please delete this message after notifying the sender. Unauthorised retention, alteration or distribution of this email is forbidden and may be actionable. Attachments are opened at your own risk and you are advised to scan incoming email for viruses before opening any attached files. We give no guarantee that any communication is virus-free and accept no responsibility for virus contamination or other system loss or damage of any kind. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Garry Gillies INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_JOB scheduling
Title: RE: DBMS_JOB scheduling Igor, you are right ... as a unwritten policy we don't allow windows databases ... even for crash test dummies ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 23, 2003 10:40 AMTo: Multiple recipients of list ORACLE-LSubject: RE: DBMS_JOB scheduling Raj, You must be speaking from "UNIX heights" -J Under Windows I find dbms_job much more reliable than windows "at" scheduling. Actually, never had problems with dbms_job "forgetting" to run a job. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Wednesday, July 23, 2003 9:24 AMTo: Multiple recipients of list ORACLE-LSubject: RE: DBMS_JOB scheduling Garry, 1. have you tried select to_char(sysdate,'D') from dual ?? This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number. nevertheless, I think what you have attempted is fantastic and worthy of adoption ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Garry Gillies [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 23, 2003 5:59 AM To: Multiple recipients of list ORACLE-L Subject: DBMS_JOB scheduling Any Interest? The DBMS_JOB package is supplied by Oracle to allow the running of procedures at regular intervals. Unfortunately the INTERVAL parameter is limited to 128 characters, which prevents you from getting very complex (user defined functions [in the interval parameter] do not work well - according to Fuerstein in his book Oracle Built In Packages). The situation is eased somewhat by the fact that the NEXT_DATE parameter can be supplied to the procedure as an in/out parameter - and the procedure can contain whatever code is necessary to calculate when next to run. This is all very well, but custom coding scheduling routines can quickly become tedious. On the basis of "do it once and get it over with" I have written a function called NEXT_DATE which I have wrapped in a package called CRON. There is a Unix program called cron which runs jobs on a regular basis. Although the scheduling data supplied to cron is simple and concise, complex schedules are easy to specify. The NEXT_DATE function takes in a cron schedule string and returns the next date that conforms to the schedule - or you can supply a cron schedule and a date and it will return the first date after the supplied date that conforms to the schedule. At the moment it is not very friendly on the error detection front. A VALUE_ERROR is returned if it deems the cron schedule to be invalid. You will also get a VALUE_ERROR if the next valid date is more than twenty seven years in the future. DBMS_OUTPUT is used to display error messages which will hopefully give you a clue. This will be improved if I receive enough complaints ( and suggestions for improvements). THE CRON SCHEDULE A cron schedule consists of five components, each separated from the next by a space. The syntax is identical for all components. The components represent Minute in Hour Hour in day Day in month Month in year Day of Week - A bit of a bugger this one. In Unix land the day numbering runs from 0-6 with 0 being Sunday. In Oracle the day numbering depends on the setting of NLS_TERRITORY. I have chosen to go with ISO standard 8601:1998 which runs from 1-7 with 1 being Monday. This is so close to the Unix convention that I can interpret Unix cron schedules correctly. Curiously, Oracle do not provide a date format which supplies this number. The ISO week number is available with the format 'IW', but not the ISO day number. If you have a field of type date called dt, you can obtain the ISO day number with ( trunc(dt) - trunc(dt ,'IW') ) + 1 A component can consist of an asterisk * which represents all valid values or a number of elements separated by a comma (if only one element is supplied, forget the comma). An element can be a single number - valid for the co
RE: DBMS_JOB scheduling
Title: RE: DBMS_JOB scheduling nope ... at-least not yet. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Garry Gillies [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 23, 2003 11:14 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB scheduling Are you looking for money? :-) Garry Gillies This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: dbms_stats via dbms_job - syntax question
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of quotes
(including none at all) around the cacscade keyword as per Raj' suggestion with still
no success.
Thomas's suggestion of using a procedure was my failback position and is probably the
way I will have to go. I am still frustrated that I cannot get the syntax to work
correctly.
Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it
fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: dbms_stats via dbms_job - syntax question SOLVED
Thanks for all the help. The problem was not with my coding but the syntax. In my
worked example I had CASCADE=TRUE whereas it should have been
CASCADE=TRUE. A subtle difference but one that caused me (and fellow listers) no end
of hassle.
So all the answers that I said were incorrect were not, they had all replicated my
original typo.
But one thing I proved was that you can mix and match positional and naming parameters
The final working version for anyone who is interested is
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCADE=TRUE);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Thanks to all
John
-Original Message-
Sent: 11 June 2003 09:24
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of quotes
(including none at all) around the cacscade keyword as per Raj' suggestion with still
no success.
Thomas's suggestion of using a procedure was my failback position and is probably the
way I will have to go. I am still frustrated that I cannot get the syntax to work
correctly.
Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it
fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
RE: dbms_stats via dbms_job - syntax question
John,
My code doesn't work probably because CASCADE = TRUE should actually
be: CASCADE = TRUE.
So, this should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Wednesday, June 11, 2003 3:24 AM
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of
quotes (including none at all) around the cacscade keyword as per Raj'
suggestion with still no success.
Thomas's suggestion of using a procedure was my failback position and is
probably the way I will have to go. I am still frustrated that I cannot
get the syntax to work correctly.
Note I can get my original PL/SQL code to work and put an entry into
dbms_jobs but it fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list
RE: dbms_stats via dbms_job - syntax question SOLVED
But one thing I proved was that you can mix and match positional and
naming parameters
Hmm, I'd still rather use one OR another: you never know if mixing them
would work in the next version...
Why trying your luck?-:)
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Wednesday, June 11, 2003 4:35 AM
To: Multiple recipients of list ORACLE-L
Thanks for all the help. The problem was not with my coding but the
syntax. In my worked example I had CASCADE=TRUE whereas it should have
been
CASCADE=TRUE. A subtle difference but one that caused me (and fellow
listers) no end of hassle.
So all the answers that I said were incorrect were not, they had all
replicated my original typo.
But one thing I proved was that you can mix and match positional and
naming parameters
The final working version for anyone who is interested is
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCAD
E=TRUE);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Thanks to all
John
-Original Message-
Sent: 11 June 2003 09:24
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of
quotes (including none at all) around the cacscade keyword as per Raj'
suggestion with still no success.
Thomas's suggestion of using a procedure was my failback position and is
probably the way I will have to go. I am still frustrated that I cannot
get the syntax to work correctly.
Note I can get my original PL/SQL code to work and put an entry into
dbms_jobs but it fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
RE: dbms_stats via dbms_job - syntax question SOLVED
John
Then be sure to set the init.ora parameter
_do_what_I_mean=true
It solved most of my problems.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, June 11, 2003 4:35 AM
To: Multiple recipients of list ORACLE-L
Thanks for all the help. The problem was not with my coding but the syntax.
In my worked example I had CASCADE=TRUE whereas it should have been
CASCADE=TRUE. A subtle difference but one that caused me (and fellow
listers) no end of hassle.
So all the answers that I said were incorrect were not, they had all
replicated my original typo.
But one thing I proved was that you can mix and match positional and naming
parameters
The final working version for anyone who is interested is
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCADE=T
RUE);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Thanks to all
John
-Original Message-
Sent: 11 June 2003 09:24
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of
quotes (including none at all) around the cacscade keyword as per Raj'
suggestion with still no success.
Thomas's suggestion of using a procedure was my failback position and is
probably the way I will have to go. I am still frustrated that I cannot get
the syntax to work correctly.
Note I can get my original PL/SQL code to work and put an entry into
dbms_jobs but it fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official
dbms_stats via dbms_job - syntax question
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The syntax I am trying
is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the line but 2 of
them around the schema name as that is a varchar2. Ideally I think I want 2 single
quotes around the cascade but I cannot get it to work properly. The above example
submits the job as
Job What
==
2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE');
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on his web site
but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: dbms_stats via dbms_job - syntax question
Title: RE: dbms_stats via dbms_job - syntax question
you don't need quotes around cascade=true ...
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
From: Hallas, John, Tech Dev [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 10, 2003 10:35 AM
To: Multiple recipients of list ORACLE-L
Subject: dbms_stats via dbms_job - syntax question
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs
The following syntax works fine to run the procedure interactively
execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as
Job What
==
2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE');
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more
John
This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*2
RE: dbms_stats via dbms_job - syntax question
John,
I think the easiest way to do this is to create a stored procedure that
calls dbms_stats for you. you could then simply run your stored procedure
from dbms_jobs.
create or replace procedure run_stats is
begin
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10
,cascade=true);
end;
and then:
declare
l_job number;
begin
dbms_job.submit (l_job,'run_stats',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
make sense?
PS. I personally don't like DBMS_JOBS. I use either Cron, or Tivoli on NT
systems to schedule all jobs.
hope this helps.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday, June 10, 2003 10:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10
,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The syntax I
am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE
=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally I
think I want 2 single quotes around the cascade but I cannot get it to work
properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE');
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on his
web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: dbms_stats via dbms_job - syntax question
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: dbms_stats via dbms_job - syntax question
Why not submit it exactly the same as you do it interactively:
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(ownname=''RPT_3G_MASTER'',estimate_percent=10,cascade=true);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
All you need to do is replace the single quotes around the schema name with
double quotes.
At 06:34 AM 6/10/2003 -0800, you wrote:
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The syntax
I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally I
think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE');
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on his
web site but it doesn't appaer to exist any more
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: submitting statspack.snap through dbms_job
If you are not already aware there is an spauto.sql in the $ORACLE_HOME/rdbms/admin directory which could be modified with the following for example: next_date=trunc(sysdate)+10/24, interval='trunc(sysdate)+10/24'); Regards Murali Zabair Ahmed [EMAIL PROTECTED] wrote: Quick question, I want to submit statspack.snap at 10:00am and 14:00pm through dbms_job, it's friday afternoon and my head is hurting me from a heavy night and the manuals are as clear as mud. Thanks Yahoo! Plus - For a better Internet experienceMurali Vallath "We must be the change we wish to see in the world." Mahatma Gandhi.Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
submitting statspack.snap through dbms_job
Quick question, I want to submit statspack.snap at 10:00am and 14:00pm through dbms_job, it's friday afternoon and my head is hurting me from a heavy night and the manuals are as clear as mud. ThanksYahoo! Plus - For a better Internet experience
Re: submitting statspack.snap through dbms_job
Title: Re: submitting statspack.snap through dbms_job var jnr number exec dbms_job.submit(job=:jnr, what='statspack.snap(5);', next_date=trunc(sysdate)+10/24, interval='trunc(sysdate)+10/24'); exec dbms_job.submit(job=:jnr, what='statspack.snap(5);', next_date=trunc(sysdate)+14/24, interval='trunc(sysdate)+14/24'); commit; At 06:23 AM 4/4/2003 -0800, you wrote: Quick question, I want to submit statspack.snap at 10:00am and 14:00pm through dbms_job, it's friday afternoon and my head is hurting me from a heavy night and the manuals are as clear as mud. Thanks http://uk.yahoo.com/mail/tagline_plus/?http://uk.promotions.yahoo.com/yplus/btoffer.htmlYahoo! Plus - For a better Internet experience Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications. Cette communication par courrier lectronique est une communication prive l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'tes pas le destinataire prvu, vous tes avis, par les prsentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'tes pas spcifiquement autoris recevoir ce courriel ou si vous croyez l'avoir reu par erreur, veuillez en aviser l'expditeur original immdiatement. Nous respectons les demandes similaires qui touchent la confidentialit des communications par courrier lectronique.
Re: Fixed_date and dbms_job
Hi, 1) check job_queue_processes and job_queue_interval parameters in your init.ora file 2) have a commit after dbms_job.submit 3) if you want the procedure to be executed every second, then the next date should be 'sysdate+1/86400' = dbms_job.submit(:job_num, 'myprocedure;',sysdate, 'sysdate+1/86400'); Mike Kader Ben wrote: Hi Listers, I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute. Could you please give me hint how to resolve this problem? Thanks you, Ben __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- 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: Fixed_date and dbms_job
Thnks Michal for your input, All seems right for me: SQL show parameter job NAME TYPEVALUE --- -- job_queue_interval integer 90 job_queue_processes integer 2 Michal I cannot put next_date to 'sysdate+1/86400' because this date will never be reached since I use ALTER SYSTEM SET FIXED_DATE = sysdate+deta Where the delta is the incremental time to simulate pseudo-time in the future. And the main objective of the procedure to do. Ben --- Michal Zaschke [EMAIL PROTECTED] wrote: Hi, 1) check job_queue_processes and job_queue_interval parameters in your init.ora file 2) have a commit after dbms_job.submit 3) if you want the procedure to be executed every second, then the next date should be 'sysdate+1/86400' = dbms_job.submit(:job_num, 'myprocedure;',sysdate, 'sysdate+1/86400'); Mike Kader Ben wrote: Hi Listers, I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute. Could you please give me hint how to resolve this problem? Thanks you, Ben __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- 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). __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben 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: Fixed_date and dbms_job
Thanks Jacques, Yes I did: VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT(:jobno, 'fixed_date_proc.getSystemDate;',sysdate, 'sysdate', true); commit; end; / Ben --- Jacques Kilchoer [EMAIL PROTECTED] wrote: After calling dbms_job.submit, did you issue a commit? -Original Message- From: Kader Ben [mailto:[EMAIL PROTECTED] I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute. __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben 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: Fixed_date and dbms_job
Ben, If this problem has not been fixed, check something else. Run: select job, what, broken, failures from dba_jobs; I don't know how many, but after a certain number of failures, a job is marked as broken and won't run automatically. Darrell [EMAIL PROTECTED] 03/14/03 08:38AM Thanks Jacques, Yes I did: VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT(:jobno, 'fixed_date_proc.getSystemDate;',sysdate, 'sysdate', true); commit; end; / Ben --- Jacques Kilchoer [EMAIL PROTECTED] wrote: After calling dbms_job.submit, did you issue a commit? -Original Message- From: Kader Ben [mailto:[EMAIL PROTECTED] I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute. __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben 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: Darrell Landrum 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: Fixed_date and dbms_job
You can't call a job every second via DBMS_JOB. I believe that at best the resolution is 1 minute. You could just open another session and run something like this: declare fd varchar2(50) := '01/01/2010 00:00:00'; v_sql varchar2(200); begin loop v_sql := 'alter system set fixed_date = ' || fd || ''; execute immediate v_sql; dbms_lock.sleep(1); fd := to_char(to_date(fd,'mm/dd/ hh24:mi:ss') + 1/86400, 'mm/dd/ hh24:mi:ss'); end loop; end; / Jared Kader Ben [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/13/2003 03:18 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Fixed_date and dbms_job Hi Listers, I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute. Could you please give me hint how to resolve this problem? Thanks you, Ben __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben 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: Fixed_date and dbms_job
Many thanks Jared, Definitely I think this is only one away to resolve my problem. Have nice week end, Ben --- [EMAIL PROTECTED] wrote: You can't call a job every second via DBMS_JOB. I believe that at best the resolution is 1 minute. You could just open another session and run something like this: declare fd varchar2(50) := '01/01/2010 00:00:00'; v_sql varchar2(200); begin loop v_sql := 'alter system set fixed_date = ' || fd || ''; execute immediate v_sql; dbms_lock.sleep(1); fd := to_char(to_date(fd,'mm/dd/ hh24:mi:ss') + 1/86400, 'mm/dd/ hh24:mi:ss'); end loop; end; / Jared Kader Ben [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/13/2003 03:18 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Fixed_date and dbms_job Hi Listers, I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute. Could you please give me hint how to resolve this problem? Thanks you, Ben __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben 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). __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben 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: Fixed_date and dbms_job
It takes 16 failures to mark the job as broken. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:24 AM Ben, If this problem has not been fixed, check something else. Run: select job, what, broken, failures from dba_jobs; I don't know how many, but after a certain number of failures, a job is marked as broken and won't run automatically. Darrell [EMAIL PROTECTED] 03/14/03 08:38AM Thanks Jacques, Yes I did: VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT(:jobno, 'fixed_date_proc.getSystemDate;',sysdate, 'sysdate', true); commit; end; / Ben --- Jacques Kilchoer [EMAIL PROTECTED] wrote: After calling dbms_job.submit, did you issue a commit? -Original Message- From: Kader Ben [mailto:[EMAIL PROTECTED] I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute. __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben 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: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Fixed_date and dbms_job
Thanks!! Darrell Landrum Database Administrator Zale Corporation [EMAIL PROTECTED] 03/14/03 12:44PM It takes 16 failures to mark the job as broken. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:24 AM Ben, If this problem has not been fixed, check something else. Run: select job, what, broken, failures from dba_jobs; I don't know how many, but after a certain number of failures, a job is marked as broken and won't run automatically. Darrell [EMAIL PROTECTED] 03/14/03 08:38AM Thanks Jacques, Yes I did: VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT(:jobno, 'fixed_date_proc.getSystemDate;',sysdate, 'sysdate', true); commit; end; / Ben --- Jacques Kilchoer [EMAIL PROTECTED] wrote: After calling dbms_job.submit, did you issue a commit? -Original Message- From: Kader Ben [mailto:[EMAIL PROTECTED] I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute. __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben 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: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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).
Fixed_date and dbms_job
Hi Listers, I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute. Could you please give me hint how to resolve this problem? Thanks you, Ben __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben 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: Fixed_date and dbms_job
Title: RE: Fixed_date and dbms_job After calling dbms_job.submit, did you issue a commit? -Original Message- From: Kader Ben [mailto:[EMAIL PROTECTED]] I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute.
dbms_job
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).
Re: dbms_job
It will submit the job and continue. Even nicer is that if you subsequently do a rollback later, then the job will be roll'd back as well. In this way, you could put dbms_job into (say) a trigger and if the statement later roll's back you don't end up with a mess hth connor --- Basavaraja, Ravindra [EMAIL PROTECTED] wrote: Hi, I have a procedure that submits a dbms_job for immediate processing for onetime.I want to know if the procedure that submits the job will have to wait till the job gets executed to execute the next piece of code after the dbms_job.submit() in the procedure or will the procedure submit the dbms_job and continue executing the next line of code in the procedure. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
recursive calls and DBMS_JOB
I'm trying to isolate high CPU consumers in a stressed application, and have noticed that I'm spending a lot of time doing recursive calls. Specifically, a high percentage of recursive calls and recursive CPU usage come from the following anonymous block: DECLARE job BINARY INTEGER :=job; next_date . My question is this: Are the recursive cpu/call stats that are accumulating a result of the procedures scheduled within DBMS_JOB (there are a handful of these procedures), or is this likely due to DBMS_JOB (and whatever logic it uses to keep itself doing what it should) itself? Thanks John Clarke Oracle DBA Centroid Systems, Inc -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Clarke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_JOB : Can submit but does not execute=working now
Hi,
Thanks to all who reply. After setting the job_queue_processes to 1 and
job_queue_interval = 90, the job runs successfully. Thank you.
Regds,
Catherine
-Original Message-
From: Jay Hostetter [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 19, 2003 8:25 PM
To: [EMAIL PROTECTED]; CHAN Chor Ling Catherine (CSC)
Subject:RE: DBMS_JOB : Can submit but does not execute
Make sure job_queue_processes 0. Also check job_queue_interval.
Jay Hostetter
Oracle DBA
D. E. Communications
Ephrata, PA USA
[EMAIL PROTECTED] 02/18/03 08:28PM
Hi Gurus,
I use the following command to execute immediately.
Connected.
SQL BEGIN
2 DBMS_JOB.RUN(1);
3 END;
4 /
PL/SQL procedure successfully completed.
--
-- I change the time for next execution to check whether it can submit
on 18 Feb 2003 at 8am
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
It did not execute. Any advice ? TIA
Regds,
Catherine
-Original Message-
From: CHAN Chor Ling Catherine (CSC)
Sent: Wednesday, February 19, 2003 9:15 AM
To: '[EMAIL PROTECTED]'
Subject:DBMS_JOB : Can submit but does not
execute
Hi Gurus,
I have submitted a job but it does not execute. Why?
Does the owner require any privileges ? However, I can use DBMS_JOB.CHANGE command to
execute immediately. I would like the job to execute on a daily basis. Any advice ?
TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4
DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- --
--
SCHEMA_USERLAST_DATE LAST_SEC
THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
-- -
- - -- -
INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV
INSTANCE
RE: DBMS_JOB : Can submit but does not execute
Title: RE: DBMS_JOB : Can submit but does not execute In 8i I have had bad luck of submitting it at sysdate and they don't fire ... Once I got burned, I can afford 5 seconds ... if it is that critical, dbms_job isn't the tool for that job anymore. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 19, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB : Can submit but does not execute That shouldn't matter. Look at DBA_JOBS when you startup an instance after it's been down for a while (not that it would ever happen!). All pending jobs (nextdate = SYSDATE) should be firing, at least as many job_queue_processes as you have in the init.ora. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Wednesday, February 19, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Also as a practice, I always submit sysdate + 5 seconds instead of sysdate. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- Sent: Wednesday, February 19, 2003 7:34 AM To: Multiple recipients of list ORACLE-L Make sure job_queue_processes 0. Also check job_queue_interval. Jay Hostetter Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: DBMS_JOB : Can submit but does not execute
Title: RE: DBMS_JOB : Can submit but does not execute Maybe this is stating the obvious, but make sure you do a COMMIT after submitting the job. - Dave -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Thursday, February 20, 2003 6:59 AMTo: Multiple recipients of list ORACLE-LSubject: RE: DBMS_JOB : Can submit but does not execute In 8i I have had bad luck of submitting it at sysdate and they don't fire ... Once I got burned, I can afford 5 seconds ... if it is that critical, dbms_job isn't the tool for that job anymore. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 19, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB : Can submit but does not execute That shouldn't matter. Look at DBA_JOBS when you startup an instance after it's been down for a while (not that it would ever happen!). All pending jobs (nextdate = SYSDATE) should be firing, at least as many job_queue_processes as you have in the init.ora. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Wednesday, February 19, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Also as a practice, I always submit sysdate + 5 seconds instead of sysdate. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- Sent: Wednesday, February 19, 2003 7:34 AM To: Multiple recipients of list ORACLE-L Make sure job_queue_processes 0. Also check job_queue_interval. Jay Hostetter Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_JOB : Can submit but does not execute
You have to give commit after submit the job.
-Original Message-
Ling Catherine (CSC)
Sent: Tuesday, February 18, 2003 8:14 PM
To: Multiple recipients of list ORACLE-L
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner
require any privileges ? However, I can use DBMS_JOB.CHANGE command to
execute immediately. I would like the job to execute on a daily basis.
Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDA
TE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USERLAST_DATE LAST_SEC THIS_DATE THIS_SEC
NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - -
- -- - INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV
INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:20
18-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENCY='AMERICA' NLS_NUME RIC_CHARACTERS='.,'
NLS_DATE_FORMAT='DD-MON-YY' NLS_DATE_LANGUAGE='AMERICAN'
NLS_SORT='BINARY'
01020002
0
Regds,
Catherine
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: CHAN Chor Ling Catherine (CSC)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ramon E. Estevez
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: DBMS_JOB : Can submit but does not execute
Have you set the job_queue_processes parameter in you init.ora file?
CHAN Chor Ling Catherine (CSC) wrote:
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner require any privileges ? However, I can use DBMS_JOB.CHANGE command to execute immediately. I would like the job to execute on a daily basis. Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 ' PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USERLAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - - - -- -
INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:2018-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUME
RIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-YY' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
01020002 0
Regds,
Catherine
--
Ing. Michal Zaschke
DB Administrator
Sokolovska uhelna, a.s.
tel.: +420 352 465417
e-mail: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Michal Zaschke
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: DBMS_JOB : Can submit but does not execute
You have to run the job after you submit it.
SQL exec dbms_job.run(24);
Also make sure all init.ora parameters are set eg (job_queue_interval ,
job_queue_processes )
HTH,
Rajesh
-Original Message-
Ling Catherine (CSC)
Sent: Tuesday, February 18, 2003 8:14 PM
To: Multiple recipients of list ORACLE-L
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner
require any privileges ? However, I can use DBMS_JOB.CHANGE command to
execute immediately. I would like the job to execute on a daily basis.
Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDA
TE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USERLAST_DATE LAST_SEC THIS_DATE THIS_SEC
NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - -
- -- - INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV
INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:20
18-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENCY='AMERICA' NLS_NUME RIC_CHARACTERS='.,'
NLS_DATE_FORMAT='DD-MON-YY' NLS_DATE_LANGUAGE='AMERICAN'
NLS_SORT='BINARY'
01020002
0
Regds,
Catherine
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: CHAN Chor Ling Catherine (CSC)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ramon E. Estevez
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rajesh Dayal
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: DBMS_JOB : Can submit but does not execute
Make sure job_queue_processes 0. Also check job_queue_interval.
Jay Hostetter
Oracle DBA
D. E. Communications
Ephrata, PA USA
[EMAIL PROTECTED] 02/18/03 08:28PM
Hi Gurus,
I use the following command to execute immediately.
Connected.
SQL BEGIN
2 DBMS_JOB.RUN(1);
3 END;
4 /
PL/SQL procedure successfully completed.
--
-- I change the time for next execution to check whether it can submit on 18 Feb 2003
at 8am
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 ' PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
It did not execute. Any advice ? TIA
Regds,
Catherine
-Original Message-
From: CHAN Chor Ling Catherine (CSC)
Sent: Wednesday, February 19, 2003 9:15 AM
To: '[EMAIL PROTECTED]'
Subject:DBMS_JOB : Can submit but does not execute
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner
require any privileges ? However, I can use DBMS_JOB.CHANGE command to execute
immediately. I would like the job to execute on a daily basis. Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USERLAST_DATE LAST_SEC THIS_DATE THIS_SEC
NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - -
- -- -
INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV
INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:20
18-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENCY='AMERICA' NLS_NUME
RIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-YY'
NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
01020002
0
Regds,
Catherine
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: CHAN Chor Ling Catherine (CSC)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received
RE: DBMS_JOB : Can submit but does not execute
Title: RE: DBMS_JOB : Can submit but does not execute
Also as a practice, I always submit sysdate + 5 seconds instead of sysdate.
Raj
-
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!
-Original Message-
From: Jay Hostetter [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 19, 2003 7:34 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: DBMS_JOB : Can submit but does not execute
Make sure job_queue_processes 0. Also check job_queue_interval.
Jay Hostetter
Oracle DBA
D. E. Communications
Ephrata, PA USA
[EMAIL PROTECTED] 02/18/03 08:28PM
Hi Gurus,
I use the following command to execute immediately.
Connected.
SQL BEGIN
2 DBMS_JOB.RUN(1);
3 END;
4 /
PL/SQL procedure successfully completed.
--
-- I change the time for next execution to check whether it can submit on 18 Feb 2003 at 8am
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 ' PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
It did not execute. Any advice ? TIA
Regds,
Catherine
-Original Message-
From: CHAN Chor Ling Catherine (CSC)
Sent: Wednesday, February 19, 2003 9:15 AM
To: '[EMAIL PROTECTED]'
Subject: DBMS_JOB : Can submit but does not execute
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner require any privileges ? However, I can use DBMS_JOB.CHANGE command to execute immediately. I would like the job to execute on a daily basis. Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2 job BINARY_INTEGER;
3 BEGIN
4 DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5 DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 ' PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - - - -- -
INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:20 18-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUME
RIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-YY' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
01020002 0
Regds,
Catherine
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: CHAN Chor Ling Catherine (CSC)
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Hostetter
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
RE: DBMS_JOB : Can submit but does not execute
That shouldn't matter. Look at DBA_JOBS when you startup an instance after it's been down for a while (not that it would ever happen!). All pending jobs (nextdate = SYSDATE) should be firing, at least as many job_queue_processes as you have in the init.ora. Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Wednesday, February 19, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Also as a practice, I always submit sysdate + 5 seconds instead of sysdate. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- Sent: Wednesday, February 19, 2003 7:34 AM To: Multiple recipients of list ORACLE-L Make sure job_queue_processes 0. Also check job_queue_interval. Jay Hostetter Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
DBMS_JOB
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).
Re: DBMS_JOB
The PL/SQL packages that we have running as jobs resubmit themselves when the job is complete. So we can just add logic before calling DBMS_JOB.SUBMIT. Can your PL/SQL be modified in this way? Jay [EMAIL PROTECTED] 02/18/03 09:14AM Hi All, Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes, unless the time of day is between X and Y (for example 00:00am 03:00am)? All help appreciated! Pointers to RTFM more than welcome! ;) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DBMS_JOB
use a function: check if 15 minutes after current time is in the don't run interval if it is return the first time after the interval if not return 15 minutes later Pat Hi All, Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes, unless the time of day is between X and Y (for example 00:00am 03:00am)? All help appreciated! Pointers to RTFM more than welcome! ;) Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pat Hildebrand INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_JOB
DOH! You know, I knew that I had read about this somewhere, but couldn't for the life of me remember where. You mentioning Tom kick started my aching brain! ;) The asktom site is down at the moment whilst being upgraded, but I still have the copy of Oracle magazine where Tom discussed this! For all others that may be interested, page 98 of Oracle Magazine (January/February 2003 edition) discusses this (Setting a Complex Interval)! Thanks Kirti! Mark -Original Message- Sent: 18 February 2003 15:09 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Mark, You may want to check http://asktom.oracle.com. I think there are a few examples that you will find come close to what you are looking for. - Kirti -Original Message- Sent: Tuesday, February 18, 2003 8:14 AM To: Multiple recipients of list ORACLE-L Hi All, Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes, unless the time of day is between X and Y (for example 00:00am 03:00am)? All help appreciated! Pointers to RTFM more than welcome! ;) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_JOB
Mark, You may want to check http://asktom.oracle.com. I think there are a few examples that you will find come close to what you are looking for. - Kirti -Original Message- Sent: Tuesday, February 18, 2003 8:14 AM To: Multiple recipients of list ORACLE-L Hi All, Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes, unless the time of day is between X and Y (for example 00:00am 03:00am)? All help appreciated! Pointers to RTFM more than welcome! ;) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_JOB
Just found an online version (in the spirit of sharing solutions ;D):
http://otn.oracle.com/oramag/oracle/03-jan/o13asktom.html
---snip---
Setting a Complex Interval
I am using DBMS_JOB, and I want to schedule a job that runs every 15 minutes
from Monday to Friday, between 6 a.m. and 6 p.m. How do I schedule it? I
cannot figure out the interval I should be passing.
Well, for figuring out complex intervals for DBMS_JOB, I like to use the new
(as of Oracle8i Release 2) CASE statement. For example, the following CASE
statement returns the correct interval for your specification:
SQL alter session set nls_date_format =
2 'dy mon dd, hh24:mi';
Session altered.
SQL select
2 sysdate,
3 case
4when (to_char( sysdate, 'hh24' )
5 between 6 and 17
6 and to_char(sysdate,'dy') NOT IN
7 ('sat','sun') )
8then trunc(sysdate)+
9 (trunc(to_char(sysdate,'s')/
10 900)+1)*15/24/60
11when (to_char( sysdate, 'dy' )
12 not in ('fri','sat','sun') )
13then trunc(sysdate)+1+6/24
14else next_day( trunc(sysdate),
15 'Mon' )+6/24
16 end interval_date
17 from dual
18 /
SYSDATE
--
INTERVAL_DATE
--
sun sep 15, 2002 16:35
mon sep 16, 2002 06:00
The CASE statement gives you great flexibility in generating a complex value
such as you need. Unfortunately, DBMS_JOB will allow you to use only an
interval that is 200 characters or less, and even if you scrunch up the
CASE statement above, you'll find it is about 300 characters minimally. So,
you cannot use it directly in the call to DBMS_JOB. My solution to that is
one of two things: either I would create a view NEXT_DATE as that select, so
that select * from next_date would return the next time the job runs, or I
would wrap the above query in a PL/SQL function that returns a date. If I
used a view, my call to DBMS_JOB might look like:
begin
dbms_job.submit
( :n, 'proc;', sysdate,
'(select * from next_date)'
);
end;
/
Or, if I used the PL/SQL function approach and created a function NEXT_DATE,
it could be:
begin
dbms_job.submit
( :n, 'proc;', sysdate,
'next_date()'
);
end;
/
---snip---
Cheers
Mark
-Original Message-
Sent: 18 February 2003 15:29
To: Multiple recipients of list ORACLE-L
DOH!
You know, I knew that I had read about this somewhere, but couldn't for the
life of me remember where. You mentioning Tom kick started my aching brain!
;)
The asktom site is down at the moment whilst being upgraded, but I still
have the copy of Oracle magazine where Tom discussed this! For all others
that may be interested, page 98 of Oracle Magazine (January/February 2003
edition) discusses this (Setting a Complex Interval)!
Thanks Kirti!
Mark
-Original Message-
Sent: 18 February 2003 15:09
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Mark,
You may want to check http://asktom.oracle.com.
I think there are a few examples that you will find come close to what you
are looking for.
- Kirti
-Original Message-
Sent: Tuesday, February 18, 2003 8:14 AM
To: Multiple recipients of list ORACLE-L
Hi All,
Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes,
unless the time of day is between X and Y (for example 00:00am 03:00am)?
All help appreciated! Pointers to RTFM more than welcome! ;)
Mark
===
Mark Leith | T: +44 (0)1905 330 281
Sales Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: [EMAIL PROTECTED]
===
http://www.cool-tools.co.uk
Maximising throughput performance
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Leith
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Leith
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from
RE: DBMS_JOB : Can submit but does not execute
Hi Gurus,
I use the following command to execute immediately.
Connected.
SQL BEGIN
2 DBMS_JOB.RUN(1);
3 END;
4 /
PL/SQL procedure successfully completed.
--
-- I change the time for next execution to check whether it can submit on 18 Feb 2003
at 8am
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 ' PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
It did not execute. Any advice ? TIA
Regds,
Catherine
-Original Message-
From: CHAN Chor Ling Catherine (CSC)
Sent: Wednesday, February 19, 2003 9:15 AM
To: '[EMAIL PROTECTED]'
Subject:DBMS_JOB : Can submit but does not execute
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner
require any privileges ? However, I can use DBMS_JOB.CHANGE command to execute
immediately. I would like the job to execute on a daily basis. Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 '
PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USERLAST_DATE LAST_SEC THIS_DATE THIS_SEC
NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - -
- -- -
INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV
INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:20
18-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENCY='AMERICA' NLS_NUME
RIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-YY'
NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
01020002
0
Regds,
Catherine
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: CHAN Chor Ling Catherine (CSC)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
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: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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: CHAN Chor Ling Catherine (CSC)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: RE: dbms_job - running jobs every 15 minutes
How about dbms_job.submit(:jobno, 'statspack.snap', sysdate+n/1440, 'sysdate 15/1440'); where n= a number of minutes to the nearest 15 minutes. So if it's 14:25 then it would be sysdate+5/1440. since you only need to do this one time, just make sure that sysdate + n = 0, 15, 30 or 45 after the hour... :-) Of course, if you want to automate the thing, then build this around a PL/SQL procedure...that calculates the value of n. Not elegant, but I think that when someone looks at DBA_JOBS they are not going to ask what the $*#(@( you were trying to do.. I subscribe to the KISS philosophy... :-) RF -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Robert, I am afraid that you will soon run into the 'slipping job' syndrom. Without any 'trunc' (or anything functionally similar), 'sysdate' in the interval happens to be the date when the job started - which may be up to one minute (usually) the time when you asked it to start. Means that you can easily slip by four minutes every hour. I agree with adding 15/1440 (one day = 1440mn), but your base time musn't be 'sysdate' but sysdate rounded to the nearest quarter of an hour. Considering that a quarter of an hour is a 96th (24 * 4) of a day you have several more or less complicated ways to do it. Vladimir (whose formula I am still trying to understand :-)) took the seconds since midnight, you can also do something such as [today 00:00] trunc(sysdate) + [current time rounded to the latest quarter of an hour] floor((sysdate - +trunc(sysdate))* 96) / 96 + 15/1440 HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: dbms_job - running jobs every 15 minutes
Personally, I tend to just submit four jobs: one at the top of hour, one at 15 past, one at 30 past, and the fourth at 45 past. To alter the frequency, just break or remove one or more of the jobs. Falls into the category of not elegant, but it works... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 9:39 PM How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Freeman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: dbms_job - running jobs every 15 minutes
That's exactly what I do. Phew! I thought I was the only one ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 8:03 AM Personally, I tend to just submit four jobs: one at the top of hour, one at 15 past, one at 30 past, and the fourth at 45 past. To alter the frequency, just break or remove one or more of the jobs. Falls into the category of not elegant, but it works... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 9:39 PM How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Freeman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes Cron? How 1980's :-)) RF Robert G. FreemanTechnical Management ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of several books you can find on Amazon.com! -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 7:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes Robert, I have solid reasons not to trust dbms_job ... it didn't work reliably in 901x. Call me retro ... but "cron" rocks ... 8:) Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes Cron? How 1980's :-)) RF This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re:RE: dbms_job - running jobs every 15 minutes
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 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. Dick Goulet Reply Separator Author: Freeman Robert - IL [EMAIL PROTECTED] Date: 1/22/2003 7:09 AM Cron? How 1980's :-)) RF Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (it's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Wednesday, January 22, 2003 7:19 AM To: Multiple recipients of list ORACLE-L I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 TITLERE: dbms_job - running jobs every 15 minutes/TITLE META content=MSHTML 6.00.2800.1126 name=GENERATOR/HEAD BODY DIVSPAN class=56935-22012003FONT face=Arial color=#ff size=2Cron? How 1980's :-))/FONT/SPAN/DIV DIVSPAN class=56935-22012003FONT face=Arial color=#ff size=2/FONT/SPANnbsp;/DIV DIVSPAN class=56935-22012003FONT face=Arial color=#ff size=2RF/FONT/SPAN/DIV DIVnbsp;/DIV PFONT face=Verdana size=2Robert G. Freeman/FONTFONT face=Times New RomanBR/FONTFONT face=Verdana size=2Technical Management ConsultantBRTUSC - The Oracle Experts www.tusc.comBR904.708.5076 Cell (it's everywhere that I am!)BRAuthor of several books you can find on Amazon.com!/FONT /P BLOCKQUOTE dir=ltr style=MARGIN-RIGHT: 0px DIV class=OutlookMessageHeader dir=ltr align=leftFONT face=Tahoma size=2-Original Message-BRBFrom:/B Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]BRBSent:/B Wednesday, January 22, 2003 7:19 AMBRBTo:/B Multiple recipients of list ORACLE-LBRBSubject:/B RE: dbms_job - running jobs every 15 minutesBRBR/FONT/DIV PFONT size=2I simplified it by using cron instead ... lt;ggt;/FONT /P PFONT size=2Raj/FONT BRFONT size=2__/FONT BRFONT size=2Rajendra Jamadagninbsp;nbsp;nbsp;nbsp;nbsp; nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; MIS, ESPN Inc./FONT BRFONT size=2Rajendra dot Jamadagni at ESPN dot com/FONT BRFONT size=2Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. /FONTBRFONT size=2QOTD: Any clod can have facts, but having an opinion is an art!/FONT /PBR PFONT size=2-Original Message-/FONT BRFONT size=2From: [EMAIL PROTECTED] [A href=mailto:[EMAIL PROTECTED];mailto:[EMAIL PROTECTED]/A]/FON T BRFONT size=2Sent: Tuesday, January 21, 2003 7:24 PM/FONT BRFONT size=2To: Multiple recipients of list ORACLE-L/FONT BRFONT size=2Subject: dbms_job - running jobs every 15 minutes/FONT /PBR PFONT size=2Feeling particularly anal the other day,nbsp; I used the following /FONTBRFONT size=2specification to/FONT BRFONT size=2run statspack at the top of the hour, 15, 30 and 45 minutes after the /FONTBRFONT size=2hour./FONT /P PFONT size=2variable jobno number;/FONT BRFONT size=2variable instno number;/FONT BRFONT size=2begin/FONT BRFONT size=2nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; select instance_number into :instno from v$instance;/FONT BRFONT size=2nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; dbms_job.submit(/FONT BRFONT size=2nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp ;nbsp;nbsp;nbsp; :jobno/FONT BRFONT size=2nbsp;nbsp
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes Cron? How RELIABLE !! -Original Message-From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes Cron? How 1980's :-)) RF Robert G. FreemanTechnical Management ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of several books you can find on Amazon.com! -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 7:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes LOL. Ok, I confess, my name's Robert and I'm a CRON user Rf Robert G. FreemanTechnical Management ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of several books you can find on Amazon.com! -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:49 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes Cron? How RELIABLE !! -Original Message-From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes Cron? How 1980's :-)) RF Robert G. FreemanTechnical Management ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of several books you can find on Amazon.com! -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 7:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared
RE: dbms_job - running jobs every 15 minutes
One thing I've learned Arup, there are 15,000 ways of doing the same thing, and a good many of those are as good as the other. Cron, dbms_job, at, whatever works for you!! Rf Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (it's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Wednesday, January 22, 2003 8:55 AM To: Multiple recipients of list ORACLE-L That's exactly what I do. Phew! I thought I was the only one ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 8:03 AM Personally, I tend to just submit four jobs: one at the top of hour, one at 15 past, one at 30 past, and the fourth at 45 past. To alter the frequency, just break or remove one or more of the jobs. Falls into the category of not elegant, but it works... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 9:39 PM How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Freeman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes
LOL. You are right about the job_scheduler in early
9i.
Had
all sorts of problems when I first started moving stuff to
9.0.1
and 9.0.2 and Oracle was no help figuring out what the
problem was.
I use
cron all the time, but I love the job scheduler for things
like
starting parallel PL/SQL threads, etc It's just so easy!
;-)
Hey, I
*LIKE* retro, listen to the 80's station all the time!
RF
Robert G. FreemanTechnical Management
ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's
everywhere that I am!)Author of several books you can find on
Amazon.com!
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22,
2003 9:49 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: dbms_job - running jobs every 15
minutes
Robert,
I have solid reasons not to trust dbms_job ... it didn't work reliably
in 901x. Call me retro ... but "cron" rocks ...
8:)
Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN
dot com
Any opinion expressed here is
personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts,
but having an opinion is an
art!
-Original Message-From: Freeman Robert - IL
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
dbms_job - running jobs every 15 minutes
Cron? How 1980's :-))
RF
Re: dbms_job - running jobs every 15 minutes
[EMAIL PROTECTED] wrote: One potential problem with DBMS_JOBS as is being discussed here is that Oracle computes the next_date at the end of the job. They do that so that if a job -- INTERVAL is a date function, evaluated immediately before the job starts -- executing... runs longer than it's schedule interval the two invocations will not run into each other. Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: dbms_job - running jobs every 15 minutes
Vladimir, I beg to disagree. The reason is that we used a number of jobs where the interval was defined as sysdate+1 and the job routinely ran for ~30 minutes every day. The result was that the job migrated over the course of a week by 3.5 hours so that instead of running at 6AM as scheduled on Monday it was running at 9:30 AM on the next Monday. I filed an iTar on the subject which resulted in OTS providing what I stated. Interval is evaluated at the end of the job, not the start. Dick Goulet Reply Separator Author: Vladimir Begun [EMAIL PROTECTED] Date: 1/22/2003 9:40 AM [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: dbms_job - running jobs every 15 minutes
Yea... I realized that after I sent the email, so I submitted my second suggestion... Thanks though for your thoughts!! Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (It's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Wednesday, January 22, 2003 2:45 AM To: Multiple recipients of list ORACLE-L How about dbms_job.submit(:jobno, 'statspack.snap', sysdate+n/1440, 'sysdate 15/1440'); where n= a number of minutes to the nearest 15 minutes. So if it's 14:25 then it would be sysdate+5/1440. since you only need to do this one time, just make sure that sysdate + n = 0, 15, 30 or 45 after the hour... :-) Of course, if you want to automate the thing, then build this around a PL/SQL procedure...that calculates the value of n. Not elegant, but I think that when someone looks at DBA_JOBS they are not going to ask what the $*#(@( you were trying to do.. I subscribe to the KISS philosophy... :-) RF -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Robert, I am afraid that you will soon run into the 'slipping job' syndrom. Without any 'trunc' (or anything functionally similar), 'sysdate' in the interval happens to be the date when the job started - which may be up to one minute (usually) the time when you asked it to start. Means that you can easily slip by four minutes every hour. I agree with adding 15/1440 (one day = 1440mn), but your base time musn't be 'sysdate' but sysdate rounded to the nearest quarter of an hour. Considering that a quarter of an hour is a 96th (24 * 4) of a day you have several more or less complicated ways to do it. Vladimir (whose formula I am still trying to understand :-)) took the seconds since midnight, you can also do something such as [today 00:00] trunc(sysdate) + [current time rounded to the latest quarter of an hour] floor((sysdate - trunc(sysdate))* 96) / 96 + 15/1440 HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: dbms_job - running jobs every 15 minutes
Well, I did consider that using 4 jobs , but my overdeveloped sense of aesthetics and professional hubris (that one mostly) required that it be done with a single job. ;) Lots of good suggestions here. Seems like everyone is always up for a puzzle. :) Jared Arup Nanda [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/22/2003 06:54 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: dbms_job - running jobs every 15 minutes That's exactly what I do. Phew! I thought I was the only one ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 8:03 AM Personally, I tend to just submit four jobs: one at the top of hour, one at 15 past, one at 30 past, and the fourth at 45 past. To alter the frequency, just break or remove one or more of the jobs. Falls into the category of not elegant, but it works... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 9:39 PM How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Freeman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing
RE: dbms_job - running jobs every 15 minutes
Thanks Robert, I like this. Simplified and still easy to read. Jared Robert Freeman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2003 08:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: dbms_job - running jobs every 15 minutes How about this... this will start the job at the top of the following hour and then schedule it every 15 minutes... dbms_job.submit(:jobno, 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Freeman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: dbms_job - running jobs every 15 minutes
Hmm... I had only given the code a cursory examination. Good thing I didn't use it. :) Jared Freeman Robert - IL [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/22/2003 10:19 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: dbms_job - running jobs every 15 minutes Yea... I realized that after I sent the email, so I submitted my second suggestion... Thanks though for your thoughts!! Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (It's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Wednesday, January 22, 2003 2:45 AM To: Multiple recipients of list ORACLE-L How about dbms_job.submit(:jobno, 'statspack.snap', sysdate+n/1440, 'sysdate 15/1440'); where n= a number of minutes to the nearest 15 minutes. So if it's 14:25 then it would be sysdate+5/1440. since you only need to do this one time, just make sure that sysdate + n = 0, 15, 30 or 45 after the hour... :-) Of course, if you want to automate the thing, then build this around a PL/SQL procedure...that calculates the value of n. Not elegant, but I think that when someone looks at DBA_JOBS they are not going to ask what the $*#(@( you were trying to do.. I subscribe to the KISS philosophy... :-) RF -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Robert, I am afraid that you will soon run into the 'slipping job' syndrom. Without any 'trunc' (or anything functionally similar), 'sysdate' in the interval happens to be the date when the job started - which may be up to one minute (usually) the time when you asked it to start. Means that you can easily slip by four minutes every hour. I agree with adding 15/1440 (one day = 1440mn), but your base time musn't be 'sysdate' but sysdate rounded to the nearest quarter of an hour. Considering that a quarter of an hour is a 96th (24 * 4) of a day you have several more or less complicated ways to do it. Vladimir (whose formula I am still trying to understand :-)) took the seconds since midnight, you can also do something such as [today 00:00] trunc(sysdate) + [current time rounded to the latest quarter of an hour] floor((sysdate - trunc(sysdate))* 96) / 96 + 15/1440 HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
