Pfthb

I am trying to create a job in my Oracle database to refresh a materialized view every five minutes. The following is the code I have made:

BEGIN
SYS.DBMS_SCHEDULER.create_job(
 job_name => 'refresh_cop_union',
 job_type => 'PLSQL_BLOCK',
 job_action => 'BEGIN DBMS_SNAPSHOT.REFRESH(''COP_UNION'',''C''); END;',
 start_date => SYSTIMESTAMP,
 repeat_interval => 'freq=minutely;interval=5',
 end_date => NULL,
 enabled => TRUE,
 auto_drop => FALSE,
 comments => 'Refresh COP_UNION matview'
);
END;
/

When I executed it I got the following error:

Error report -

ORA-27477: "MASTER_DB"."REFRESH_COP_UNION" already exists

ORA-06512: at "SYS.DBMS_ISCHED", line 175

ORA-06512: at "SYS.DBMS_SCHEDULER", line 288

ORA-06512: at line 2

27477. 00000 - ""%s"."%s" already exists"

*Cause: An attempt was made to create an object with a name that has
already been used by another object in the same schema.

*Action: Reissue the command using a different name or schema.

So it seems I have another object in my database with the same name, probably another job. Therefore I tried to execute the following code to delete it and replace it with my new version:

BEGIN
 sys.dbms_scheduler.drop_job(job_name => 'refresh_cop_union');
END;
/

But it failed with the following error:

Error report -

ORA-27475: unknown job "MASTER_DB"."REFRESH_COP_UNION"

ORA-06512: at "SYS.DBMS_ISCHED", line 274

ORA-06512: at "SYS.DBMS_SCHEDULER", line 753

ORA-06512: at line 2

27475. 00000 - "unknown %s "%s"."%s""

*Cause: The specified object did not exist, privileges were not granted,
or the object was of the wrong type.

*Action: Specify an object of the correct type on which you have privileges.

Which basically says that the object I am trying to delete doesn't exist. How is this possible? How can I find this object, see what it is and eventually delete it?
I also went through all the objects that may have a similar name in my database: other tables, views, triggers but I found nothing.

I tried to list all the jobs I have in order to find mine with:

SELECT * FROM USER_JOBS
WHERE WHAT LIKE '%COP_UNION%';

But nothing showed up. Any idea?

You must select USER_SCHEDULER_JOBS rather then USER_JOBS:

SELECT * 
FROM USER_SCHEDULER_JOBS
where JOB_NAME LIKE '%COP_UNION%';

DBMS_SCHEDULER are (new) scheduler jobs, whereas (old) jobs are maintained by package DBMS_JOB

Looks like a scheduler job must not have the same name as any other object. Check with

SELECT * 
FROM ALL_OBJECTS 
WHERE OBJECT_NAME LIKE '%COP_UNION%';

and chose a different name.

Seems to be a bug (Bug 4055853) in Oracle. According to Oracle the solution/workaround is

Changing the job name to be different than any object owned by the
user will work around the problem.