This post is following a question I found on LinkedIn. A DBA pasted a strange test case in 11.2.0.4 and I managed to reproduce it in 12.1.0.2 (non-multitenant) and 12.2 (multitenant). But that’s not where the story ends, I wanted to understand what’s going on, so I did some research about it and the result is this post.
Testcase
First, let’s describe the problem. When using the scheduler, the job_name is like an object in the schema and it cannot have the same name as an existing object. The test case is to create a procedure, drop it, then try to create a job with the same name. We expect it to succeed, as the procedure was dropped, but it fails:
SQL> CREATE OR REPLACE PROCEDURE testobj AS BEGIN NULL; END; / Procedure created. SQL> DROP PROCEDURE testobj; Procedure dropped. SQL> BEGIN DBMS_SCHEDULER.create_job ( job_name => 'TESTOBJ', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN null; END;', start_date => systimestamp, repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0;', enabled => TRUE); END; / BEGIN * ERROR at line 1: ORA-27477: "DEF"."TESTOBJ" already exists ORA-06512: at "SYS.DBMS_ISCHED", line 175 ORA-06512: at "SYS.DBMS_SCHEDULER", line 288 ORA-06512: at line 2
However, if we now create a table with the same name after we dropped the procedure and then remove it, the job can be created.
Research
So now it’s time for some research. I checked DBA_OBJECTS but as expected I couldn’t find the dropped procedure. I knew that the object is there somewhere, I just needed to find it. It wasn’t in the recycle bin either (duh, procedures don’t go to the recycle bin). The next thing was to check the low level table OBJ$, and voila, it’s there.
Since it wasn’t in DBA_OBJECTS but it was in OBJ$, I needed to understand what happened to it. I opened the DBA_OBJECTS view to check the code (it queries the view _CURRENT_EDITION_OBJ which then queries OBJ$, but that’s not really relevant for this issue). In the source SQL I saw all the different object type numbers (OBJ$.TYPE# represents the object type, so 2 is table, 4 is view, 7 is procedure and so on). Then I checked the TYPE# of my dropped object and it was 10. The strange thing is that 10 is not in the DBA_OBJECTS view. Now it was time for some google search. Took me some time and some digging but I managed to find Jonathan Lewis‘s post about non-existent objects. In this post he explains that dropped objects are changed to type# 10 instead of being dropped for optimization purposes. Oracle should be able to reuse the name if we create a new object with the same name before restarting the database.
Back to our case. When we drop the procedure it can still be found in OBJ$ with type=10. If we create a table after the procedure is removed, the table is created and it reuses the procedure name. But if we don’t create a table, we have the dropped procedure with type 10, and it seems that DBMS_SCHEDULER cannot reuse the object name. For me, this seems like a bug specific to DBMS_SCHEDULER mechanism. So far I couldn’t find a known bug about it, will update here if I find something.
Update
I wanted to thanks Paul Sammy that found the bug for me (Bug 25979086). The bug states that it happens in 12.2.0.1 and above (which we know is not true) and is fixed in 12.2.0.2 (which doesn’t exists, but I hope it is fixed in 18c).