2] dbms_job.submit() needs a COMMIT Regards B S pradhan
On Sun, 24 Aug 2003 [EMAIL PROTECTED] wrote : >Hello list > >Q1. How can a dba remove the a job scheduled by another user ? >DBMS_JOB does not allow a user to touch any jobs except their >own. > >Q2. Every time I schedule a job using dbms_job it doesn't show up >properly >in dba_jobs or all_jobs or user_jobs until I logoff and log on >again . >Let me explain : I have scheduled a job as scott on my test >database >9iRelease 2 9.2.0.1.0 on windows. > >There is a table called Test in the scott schema . It has a >single column of >type date. I want to insert date into it every 30 seconds. > >I ran the following as scott : > >variable jobno number ; >variable instno number ; > >begin > > select instance_number into :instno from v$instance; > > dbms_job.submit ( job => :jobno, what => 'insert into >scott.test >values( sysdate ); commit ; ', next_date => trunc (sysdate + >1/(24*60), >'MI'), interval => 'sysdate + 1 / (24*60*2)', no_parse => true, >instance => >:instno, force => false ) ; > >end ; >/ > >print jobno > >This completed sucessfully. at time 13:13:43 hrs >Now I check user_jobs > >select JOB, last_date, next_date, next_sec > from user_jobs where job = :jobno; > > JOB LAST_DATE NEXT_DATE >---------- ---------------------- >----------------------- > 5 24-aug-003 >13:14:00 > >I checked again at 13:14:30 , 13:15:00 , 13:15:30 , 13:16:00 , >13:16:30 , >etc >and it always says the same as above. (null value in last_date >and 13:14:00 >as next_date) > >I logout at 13:17:00 and log back in. Now when I run the query I >get > > JOB LAST_DATE NEXT_DATE >---------- ------------------- >------------------- > 7 24-aug-003 13:17:05 24-aug-003 13:17:35 > >and the scott.test table has a single value >select * from scott.test ; > >24-aug-003 13:17:05 > >Any ideas ? > > >-- >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). ___________________________________________________ Art meets Army ; Swapna Weds Capt. Rajsekhar. Find interesting matches on Rediff Matchmaker ! Visit http://matchmaker.rediff.com?1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: bhabani s pradhan INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
