Oracle-L: Killing jobs

From: <bulbultyagi_at_now-india.net.in>
Date: Sun, 24 Aug 2003 00:59:33 -0800
Message-ID: <F001.005CCC60.20030824005933@fatcity.com>

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: <bulbultyagi_at_now-india.net.in
INET: bulbultyagi_at_now-india.net.in

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Sun Aug 24 2003 - 03:59:33 CDT