1. AFAIK, only the owner of a job can remove it.
2. Issue a "COMMIT" after you have submitted the job. You should then see it immediately
in DBA_JOBS or USER_JOBS and it would be scheduled to run.
When you log out of sqlplus, you are issuing an implicit commit and that is why
you can see the job the next time you log in.
Hemant
At 12:59 AM 24-08-03 -0800, you 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).
Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
