Reuse Object IDs in Oracle Database 19c and 23ai

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.

Reuse Object IDs in Oracle Database 19c and 23ai

Photo by Rosie Sun on Unsplash

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:


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.

Reuse Object IDs in Oracle Database 19c and 23ai

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.

Reuse Object IDs in Oracle Database 19c and 23ai

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.


Further Links and Information


Share this: