Schroedingers Job - a job exists but doesn't at the same time.

From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
Date: Mon, 16 Dec 2013 12:10:56 +0100
Message-ID: <52AEDFC0.5070002_at_mgm-tp.com>



Hello list,

I have a strange problem when trying to create a job using the DBMS_SCHEDULER package:

I verified that the job name does not exists using the following statements: (All the following statements have been run using the same Oracle user: MY_USER)

select count(*)
from all_scheduler_jobs
where upper(job_name) = 'MY_JOBNAME';
==> returns 0

select count(*)
from all_scheduler_running_jobs
where upper(job_name) = 'MY_JOBNAME';
==> returns 0

select count(*)
from all_jobs
where upper(what) like '%MY_JOBNAME%'
==> returns 0

select count(*)
from all_scheduler_chains
where upper(chain_name) = 'MY_JOBNAME';
==> returns 0

select count(*)
from all_scheduler_groups
where upper(group_name) = 'MY_JOBNAME';
==> returns 0

select count(*)
from all_objects
where upper(object_name) = 'MY_JOBNAME';
==> returns 0

execute DBMS_SCHEDULER.drop_job(job_name => 'MY_JOBNAME', force => true);
==> ORA-27475: "MY_USER.MY_JOBNAME" must be a job

But when I try to create a job with that name using:

execute DBMS_SCHEDULER.create_job(job_name => 'MY_JOBNAME', job_type => 'PLSQL_BLOCK', job_action => 'begin ... end;', enabled => TRUE);

I get the following error: ORA-27477: "MY_USER.MY_JOBNAME" already exists

If I use a different job name (e.g. MY_JOBNAMEX) I can create and drop the job without problems.

Does anyone have an idea on where else I could look for an object with that name that prevents creating the job?

I am using Oracle 11.2.0.3 on CentOS

Thanks in advance
Thomas

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Dec 16 2013 - 12:10:56 CET

Original text of this message