你知道Oracle object_id最大是多少吗？

    我们知道Oracle数据库中创建一个新的对象无论是表、还是index都会创建一个全局唯一标识object_id（其实还有个data_object_id）。同时我们也知道本质上去是读取obj$._next_object来实现的。

    那么Oracle数据库中究竟能创建多少个对象呢？实际上以前我们确实遇到过一些客户的环境，频繁的创建和drop 对象，最后达到了object_id最大值，最终导致数据库无法正常使用，只能重建库。

    首先我们来看下官方文档的一些说明：

    从文档说明来看，在12c版本中最大支持大约是42亿个对象，按理说这个值已经很大很大了，毕竟我见过最为庞大的Oracle系统，是其中包含300多万个对象。别说300万了，之前我们一个客户100多万对象，使用xtts做跨平台迁移，其中元数据导出导入接近20个小时，非常夸张。

    在23c之前的版本中，对于Oracle object_id来讲，实际上是无法重用的。如果你遇到一些垃圾应用，频繁drop、create操作的数据库系统，那么很可能就会遇到类似问题。这里我贴一下几年前我的处理方式：

SQL> SELECT dataobj# FROM SYS.obj$ WHERE NAME = '_NEXT_OBJECT';


DATAOBJ#
----------
87370


SQL> drop table t1;


Table dropped.


SQL> c/t1/t2
1* drop table t2
SQL>


Table dropped.


SQL> c/t2/t3
1* drop table t3
SQL>


Table dropped.


SQL> c/t3/t4
1* drop table t4
SQL>


Table dropped.


SQL> c/t4/t5
1* drop table t5
SQL>


Table dropped.


SQL> conn as sysdba
Connected.
SQL> purge dba_recyclebin;


DBA Recyclebin purged.


SQL> select obj#,dataobj# ,name FROM SYS.obj$ WHERE dataobj# > 87364 order by 2;


OBJ# DATAOBJ# NAME
---------- ---------- ------------------------------
1 87375 _NEXT_OBJECT


SQL> update SYS.obj$ set DATAOBJ#=87365 where NAME='_NEXT_OBJECT';


1 row updated.


SQL> commit;


Commit complete.


SQL> conn roger/roger
Connected.
SQL> create table t1 as select * from sys.dba_objects where rownum < 100;


Table created.


SQL> c/t1/t2
1* create table t2 as select * from sys.dba_objects where rownum < 100
SQL>


Table created.


SQL> c/t2/t3
1* create table t3 as select * from sys.dba_objects where rownum < 100
SQL>


Table created.


SQL> select obj#,dataobj# ,name FROM SYS.obj$ WHERE dataobj# > 87364 order by 2;


OBJ# DATAOBJ# NAME
---------- ---------- ------------------------------
87373 87373 T1
87374 87374 T2
87375 87375 T3
1 87380 _NEXT_OBJECT


SQL>
SQL> SELECT CASE
2 WHEN (nextobjnum - maxobjnum) > 0
3 THEN 'GOOD'
ELSE 'BAD'
4 5 END "OBJ_NUM_STATE"
6 FROM (SELECT (SELECT dataobj#
7 FROM SYS.obj$
8 WHERE NAME = '_NEXT_OBJECT') nextobjnum,
9 (SELECT MAX (obj#)
10 FROM SYS.obj$) maxobjnum
11 FROM DUAL);


OBJ_NUM_STAT
------------
GOOD

   是的，这种直接操作数据字典的方法，比较粗暴，如果是生产库风险还是蛮高的。那么有没有更好的方法呢？在Oracle 23c版本之前，比如在19c版本中，如果你安装一个特定Patch Bug 33333616 （ is included in the 19.19 DBRU (April 2023 Database Release Update), and the fix for Bug 35271571 is included in the 19.21 DBRU (October 2023 Database Release Update). Both of these fixes are in version 23c.），那么可以使用一个Oracle引入的新功能。该Patch会引入一个新的参数_reuse_object_numbers 以及新的存储过程，objnum_reuse_holes。更为详细的信息，大家可以去参考  Mechanism to Recycle Database Object Identifiers (Doc ID 2923706.1)。

 对于重用，其实最重要的是要找到其中的空洞部分，然后加以利用即可。

 既然文档提到23c版本之前，那么也就意味着Oracle 23c 针对这个小的机制应该会有新的增强或者变化，因此我们来看看23c 里面究竟是如何的。

SQL> alter session set container=enmopdb1;


Session altered.


SQL> SELECT dataobj# FROM SYS.obj$ WHERE NAME = '_NEXT_OBJECT';


DATAOBJ#
----------
311274


SQL> create table tt1 as select * from dba_objects where 1=2;


Table created.


SQL> c/tt1/tt2
1* create table tt2 as select * from dba_objects where 1=2
SQL>


Table created.


SQL> c/tt2/tt3
1* create table tt3 as select * from dba_objects where 1=2
SQL>


Table created.


SQL> c/tt3/tt4
1* create table tt4 as select * from dba_objects where 1=2
SQL>


Table created.


SQL> c/tt4/tt5
1* create table tt5 as select * from dba_objects where 1=2
SQL>


Table created.


SQL> drop table tt1;


Table dropped.


SQL> drop table tt2;


Table dropped.


SQL> drop table tt3;


Table dropped.


SQL> drop table tt4;


Table dropped.


SQL> select obj#,dataobj# ,name FROM SYS.obj$ WHERE name='TT5';


OBJ# DATAOBJ# NAME
---------- ---------- ------------------------------
311278 311278 TT5


SQL> drop table tt5;


Table dropped.


SQL> SELECT dataobj# FROM SYS.obj$ WHERE NAME = '_NEXT_OBJECT';


DATAOBJ#
----------
311279


SQL> SELECT dataobj# FROM SYS.obj$ WHERE NAME = '_NEXT_OBJECT';


DATAOBJ#
----------
311279


SQL> create table tt5 as select * from dba_objects where 1=2;


Table created.


SQL> c/tt5/tt6
1* create table tt6 as select * from dba_objects where 1=2
SQL>


Table created.


SQL> c/tt6/tt7
1* create table tt7 as select * from dba_objects where 1=2
SQL>


Table created.


SQL> select obj#,dataobj# ,name FROM SYS.obj$ WHERE name like 'TT%';


OBJ# DATAOBJ# NAME
---------- ---------- ------------------------------
279158 279158 TT
311279 311279 TT5
311280 311280 TT6
311281 311281 TT7

    可以看到被drop的对象，最大object_id是311278，在最新版的23c中可以通过查询这个表来监控有多少ID可以被重用，我们来看下最大可以被重用的max object_id是也等于311278.

SQL> select count(*) from SYS.OBJNUM_REUSE;


COUNT(*)
----------
       440                                                                  DATE


SQL> select max(obj#) from SYS.OBJNUM_REUSE;


MAX(OBJ#)
----------
311278

不过这里暂时还没有研究清楚什么时候能开始重用，看上去跟新新引入的一个参数有关,我发现该参数在11204版本就已经引入了，如下：

NAME VALUE DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_object_reuse_bast 2 if 1 or higher, handle object reuse

这里我们来简单模拟重现下达到max object id 阈值的情况。

+++Oracle 11204

SQL> shutdown abort;
ORACLE instance shut down.
SQL> conn as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.


Total System Global Area 3423965184 bytes
Fixed Size 2258040 bytes
Variable Size 771754888 bytes
Database Buffers 2634022912 bytes
Redo Buffers 15929344 bytes
Database mounted.
Database opened.
SQL> set lines 200
SQL> select obj#,dataobj# ,name FROM SYS.obj$ WHERE name like 'ENMO%';


OBJ# DATAOBJ# NAME
---------- ---------- ------------------------------
4254950911 4254950911 ENMO1
121765 121765 ENMO2
121766 121766 ENMO3
121767 121767 ENMO4
121768 121768 ENMO5
121769 121769 ENMO6


6 rows selected.


SQL> SELECT dataobj# FROM SYS.obj$ WHERE NAME = '_NEXT_OBJECT';


DATAOBJ#
----------
4254950911


SQL> create table enmo7 as select * from dba_objects where 1=2
2 ;
create table enmo7 as select * from dba_objects where 1=2
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkdlron-max-objid], [4254950911], [4254950916], [], [], [], [], [], [], [], [], []



+++Oracle 23c

[oracle@db1 ~]$ sqlplus "/as sysdba"


SQL*Plus: Release 23.0.0.0.0 - Beta on Mon Feb 19 20:38:44 2024
Version 23.1.0.0.0


Copyright (c) 1982, 2022, Oracle. All rights reserved.




Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.1.0.0.0


SQL> update (select * from sys.obj$ where obj#=1) set DATAOBJ#=4254950911;


1 row updated.


SQL> commit;


Commit complete.


SQL> alter session set container=enmopdb1;


Session altered.


SQL> update (select * from sys.obj$ where obj#=1) set DATAOBJ#=4254950911;


1 row updated.


SQL> commit;


Commit complete.


SQL> shutdown abort;
Pluggable Database closed.
SQL> alter pluggable database enmopdb1 open;


Pluggable database altered.


SQL> SELECT dataobj# FROM SYS.obj$ WHERE NAME = '_NEXT_OBJECT';


DATAOBJ#
----------
4254950911


SQL> alter session set container=enmopdb1;


Session altered.


SQL> SELECT dataobj# FROM SYS.obj$ WHERE NAME = '_NEXT_OBJECT';


DATAOBJ#
----------
4254950911


SQL> create table killdb5 as select * from dba_objects where 1=2;


Table created.


SQL> create table killdb6 as select * from dba_objects where 1=2;


Table created.


SQL> select obj#,dataobj# ,name FROM SYS.obj$ WHERE name like 'KILLDB%';


OBJ# DATAOBJ# NAME
---------- ---------- ------------------------------------------------------
311286 311286 KILLDB1
311287 311287 KILLDB2
311288 311288 KILLDB3
311289 311289 KILLDB4
25797 25797 KILLDB5
25788 25788 KILLDB6


6 rows selected.


SQL>



可以看到仍然可以创建对象，成功实现了对象重用，并不会立马报错。

在Oracle 19c+版本中，如果真的遇到object id达到最大值的情况下，为了避免数据库crash，Oracle也提供了一个event来规避：

alter system set events '28632799 trace name context forever, level x';


其中相关level的解释如下：
1 = don't print warnings if OBJ# is running out
2 = don't print warnings if CON# is running out
4 = don't crash if OBJ# is about to run out
8 = don't crash is CON# is about to run out

简单记录一下，写着玩～～～