Object_id

Update (a couple of days later): I had thought this was going to be a purely geek note but it has resulted in two people calling out a nasty bug that they hit around the 19.12 / 19.13 timeline that produced “duplicate key” errors in the sys.obj$ index i_obj1. Start reading from comment #2 below – also reference MOS Doc ID 2951100.1 “Drop And Recreate Of Tables Resulted In ORA-00001: Unique Constraint (SYS.I_OBJ1) Violated.”

This is a note that will probably be of no practical use to anyone – but someone presented me with the question so I thought I’d publish the answer I gave:

Do you know how object ID is assigned? It doesn’t look as if a sequence is used

I’m fairly sure the mechanism has changed over versions. (Including an enhancement in 23c where the object number of a dropped (and purged) object can be reused.)

I don’t really know what Oracle does, but I do know that there is an object in obj$ called “_NEXT_OBJECT” and Oracle uses that as a control mechanism for the dataobj# and obj# (data_object_id and object_id) in some way. I think Oracle uses the row a bit like the row used by a sequence in seq$ – the dataobj# is bumped by a small value (seems to be 5) each time it is reached (cp. seq$.highwater for a sequence) and it’s possible that the obj# is used to record the instance number of the instance that bumped it. The instance then (I think) has a small cache of obj# values it can use before it has to read and update the “_NEXT_OBJECT” row again.

Footnote.

You might note that this description means that it is the dataobj# that actually drives the generation of a new obj# / object_id. You can demonstrate this most easily (if you have sole access to the database) by:

• creating a table,
• checking its object_id and data_object_id (which will match),
• moving it a couple of time (which will increment the data_object_id – and only the data_object_id – each time),
• creating another table.

The second table will have an object_id that is one more than the current data_object_id of the first table.