Non-existent Objects (and possibly a bug)

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).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s