I love to learn something new every day. And thanks to my Dutch friend, Robert Pastijn, I learned about a limitation of how many objects you can create in an Oracle database. Nothing to worry about, the limit is very high at a little bit over 4.2 billion. But in order to prevent hitting it, we introduced a mechanism to Reuse Object IDs in Oracle Database 19c and 23ai.
What is the mechanism?
First of all, this is ON and enabled in Oracle Database 23ai by default. But in case you need this recycle functionality in Oracle Database 19c, you will find it being introduced since Oracle 19.19.0 Release Update (RU).
The idea behind this feature is that we can easily and quickly reuse object numbers in a database and get way more headroom.
MOS Note: 2923706.1 – Mechanism to Recycle Database Object Identifiers is very well written, and incredibly detailed. Really worth reading.
What is the potential impact?
Let me quote MOS Note: 2923706.1 here:
The Oracle RDBMS has some internally imposed limits due to internal datatypes used. One such limit concerns the number of object identifiers that can be created in the lifetime of a database. This limit affects the number of objects that can be created, the number of DDL operations that can be executed against these objects, and the number of lightweight jobs that can be created. Hitting this limit is catastrophic – the database essentially becomes unusable since no new objects can be created, DDL cannot be run on the objects, and no lightweight jobs can be run. If the database is shut down, it may not be possible to reopen it.
If you want to learn more about the limits, please see MOS Note: 2660231.1 – Internal Database Limits on Number of Objects, Constraints, and Users.
How do you enable it?
In Oracle Database 23ai, it is on by default. But in Oracle Database 19c from 19.19.0 onward, you need to enable it manually. A new underscore parameter gets introduced with Oracle 19.19:
_reuse_object_numbers
The default in 19c is 0 (zero), the feature remains to be turned off by default. To enable it, set it to 5 (five). This is also already the default in Oracle Database 23ai.
alter system set "_reuse_object_numbers"=5;
No restart is required.
New underscore parameter _resuse_object_numbers – default in 19c is 0, needs to be set to 5 to activate
As soon as this feature is activated, any objects being dropped will have their object number being recycled. “Dropped” specifically means that the identifier got removed from OBJ$. In case you have the recycle bin ON, recycling does not happen for dropped objects since they still exist in the database (just under a different name starting usually with BIN$). Since I am not a fan of the recycle bin, I turn it OFF generally with:
alter system set recyclebin='OFF' scope=spfile;
Unfortunately, you need to restart the database in order to have this take effect.
New table, new procedure
In order to support this feature, a new table and a new procedure get introduced with Oracle 19.19 as well.
select OBJ#, INS_DATE from OBJNUM_REUSE order by 1;
The table caches the object numbers to be reused. But when you query it at first, it is empty.
Now, before you read on what I did, be very aware what MOS Note: 2923706.1 – Mechanism to Recycle Database Object Identifiers tells you:
The procedure must only be run when the database is in restricted mode, with no activity on the system. It would be safer still to shut down services and the listener to insure the database is isolated.
Therefore, you invoke the population for the first time with this procedure OBJNUM_REUSE_HOLES once your database is not very active:
exec objnum_reuse_holes(100000);
In my tiny toy database, I immediately had over 4500 object numbers to be cached in the table, and ready to be recycled.
New PROCEDURE to populate the table manually with reusable object numbers
Additional information
As final note, this feature does not exist in Oracle Database 21c.
You can refer to bug 33523831 in order to request a backport if needed. There is one issue seen (and this is why Robert alerted me) where the procedure OBJNUM_REUSE_HOLES being invalid after an unplug/plug upgrade from Oracle 19c to Oracle 23ai. I personally did not bump into it yet but wanted to give heads up. It gets tracked under bug 36715007 and is under further investigation, Conclusion got reached that the procedure OBJNUM_REUSE_HOLES is not needed in 23ai.
In case you get an error during plugin/upgrade, run the script ?/rdbms/admin/objnum_reuse_holes.sql in the CDB$ROOT. This is at least what the developer named as a legit workaround instead of removing the procedure.
More additional information
Oh well, I learn something new every day, mostly through customers or colleagues having me dig into things or just the bug database. Today, I learned that there is one important open issue with the recycling of object IDs: Object IDs for scheduler jobs will not be recycled. This is a serious issue for a customer I got asked to assist with.
- Bug 35666280 – CN: JOB NOT RECYCLED INTO SYS.OBJNUM_REUSE WHEN USING DBMS_SCHEDULER.CREATE_JOB/DBMS_SCHEDULER.DROP_JOB
- Bug 35859119 – OBJECT NUMBER REUSE DOES NOT CAPTURE DROPPED SCHEDULER JOBS OBJECT NUMBERS
The latter of the two, Bug 35859119, is the one where work is done on right now. Both bugs are non-public, hence no links added here.
Further Links and Information
- MOS Note: 2923706.1 – Mechanism to Recycle Database Object Identifiers
- MOS Note: 2660231.1 – Internal Database Limits on Number of Objects, Constraints, and Users
- MOS Note: 804733.1 – What is the Maximum Datafile Size Limit In an Oracle Database?
- MOS Note: 1372905.1 – What Is The Maximum Tablespace Size And Database Limit For An Oracle Database ?
–Mike
The warnings in the Support document “Only one session must run the OBJNUM_REUSE_HOLES procedure at any given time. Invoking this procedure concurrently from multiple sessions can result in wrong results being captured, leading to similar undesirable side effects as noted in the previous point.” and “Failure to follow this guideline may result in ORA-600 errors or other unpredictable side effects, possibly resulting in a database corrupted beyond repair.” remind us how the SYS schema is different from “user” schemas when we talk of concurrency and DML.
Absolutely – and I am not certain whether these warnings are really needed since the mechanism runs fully automated in 23ai without the risk to destroy the database. Actually, I am not the owner of the procedure, but I found this a pretty stark warning.
Cheers,
Mike
could you rephraze this:
“Please also note that – in case you have the recycle bin ON – this is not the case”
done.
Cheers,
Mike
Interesting. I would recommend you post an article on the limit of the number of database datafiles that can be assigned to a tablespace. We hit that years ago when we tried to add a datafile via a program that we executed daily to ensure we had adequate storage. We had to take a downtime to move objects to a new tablespace. We had already hit the 1023 max datafiles limit.
Hi Ed,
please see:
Oracle Support Document 804733.1 (What is the Maximum Datafile Size Limit In an Oracle Database?) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=804733.1
Oracle Support Document 1372905.1 (What Is The Maximum Tablespace Size And Database Limit For An Oracle Database ?) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=1372905.1
Cheers,
Mike
Hi Mike,
I just hit the problem with procedure OBJNUM_REUSE_HOLES being invalid after upgrade from 19c to 23ai. Is there any more info about bug 36715007 anywhere and what to do about this? I couldn’t find the bug in Oracle Support.
Hi Ville,
I needed to correct my reply. Initially I was going to do this:
SQL> alter session set “_oracle_script”=true;
Session altered.
SQL> drop procedure sys.OBJNUM_REUSE_HOLES;
Procedure dropped.
–> but this is not ideal. It is better to rather run the script to create the procedure in the CDB as well.
–> objnum_reuse_holes.sql
–> this will create the missing procedure and workaround the upgrade error.
SQL> drop public synonym XML_DIAG;
Synonym dropped.
SQL> drop function sys.XML_DIAG;
Function dropped.
It works – and the owner of the OBJNUM_REUSE_HOLES procedure is going to fix this (the transaction is in code review already).
I think you can drop it as described above since the procedure is not needed anymore in 23ai.
Cheers,
Mike
Hi Mike,
thanks for the quick reply. I dropped the procedure and everything seems ok. There was no synonym or procedure called xml_diag.
Regards, Ville
Thanks Ville!
There should be no impact since it is fully independent.
Cheers
Mike
Seems incredible but there are situation where rdbms reach the maximim number of objects of 4.2 billion
This feature was not yes released
Bad morning for DBA invelved
Yep!!
Mike