今天同事反馈有客户环境遇到ORA-00600: [kkdlron-max-objid], [4254950911] 报错；该环境超过30tb大小；处理起来比较麻烦。
从错误来看，应该就是应用不断drop重建对象，导致object_id达到Oracle最大值了。虽然Oracle Mos提供了一个Bug，如下：
但实际上即使打上Patch，能用的object_id 范围也很小了，也就几千万。不足以支撑3天。
在Oracle数据库中关于object/cosntraint/users的最大数量限制，可以参考这篇文档。Internal Database Limits on Number of Objects, Constraints, and Users (Doc ID 2660231.1)
言归正传；很明显这个问题，根本原因在于应用架构设计问题，不应该不断drop重建对象，其实可以使用temporary table来解决这个问题。
就这个问题而言，同事查询该数据object 仅仅不到20万个对象。那么针对这个问题，能否有一些方法可以绕过呢？
我们知道oracle创建对象是通过_next_object来获取id的，该对象属于sys；如下：
SQL> SELECT dataobj# FROM SYS.obj$ WHERE NAME = '_NEXT_OBJECT';
DATAOBJ#
----------
87365
下面我们来进行简单的测试：
SQL> conn /as sysdba
Connected.
SQL> create user roger identified by roger;
User created.
SQL> grant connect,resource,dba to roger;
Grant succeeded.
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> c/t3/t4
1* create table t4 as select * from sys.dba_objects where rownum < 100
SQL> /
Table created.
SQL> c/t4/t5
1* create table t5 as select * from sys.dba_objects where rownum < 100
SQL> /
Table created.
SQL>
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
我们可以看到，其实完全可以重用object_id。但是这里需要注意的是，直接修改数据字典，存在一定风险。需要慎重评估。
另外对于这个临时处理方案，主要是要寻找到object id到空洞范围，最好是范围够大。
当然，如果担心数据字典有异常，可以使用Oracle提供到脚本check一次。
SQL> @/tmp/hcheck.sql
HCheck Version 07MAY18 on 07-DEC-2020 11:48:40
----------------------------------------------
Catalog Version 11.2.0.4.0 (1102000400)
db_name: KILLDB
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj ... 1102000400 <= *All Rel* 12/07 11:48:41 PASS
.- MissingOIDOnObjCol ... 1102000400 <= *All Rel* 12/07 11:48:41 PASS
.- SourceNotInObj ... 1102000400 <= *All Rel* 12/07 11:48:41 PASS
.- OversizedFiles ... 1102000400 <= *All Rel* 12/07 11:48:43 PASS
.- PoorDefaultStorage ... 1102000400 <= *All Rel* 12/07 11:48:43 PASS
.- PoorStorage ... 1102000400 <= *All Rel* 12/07 11:48:43 PASS
.- TabPartCountMismatch ... 1102000400 <= *All Rel* 12/07 11:48:43 PASS
.- OrphanedTabComPart ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- MissingSum$ ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- MissingDir$ ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- DuplicateDataobj ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- ObjSynMissing ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- ObjSeqMissing ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- OrphanedUndo ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- OrphanedIndex ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- OrphanedIndexPartition ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- OrphanedIndexSubPartition ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- OrphanedTable ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- OrphanedTablePartition ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- OrphanedTableSubPartition ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- MissingPartCol ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- OrphanedSeg$ ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- OrphanedIndPartObj# ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- DuplicateBlockUse ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- FetUet ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- Uet0Check ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- SeglessUET ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- BadInd$ ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- BadTab$ ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- BadIcolDepCnt ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- ObjIndDobj ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- TrgAfterUpgrade ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- ObjType0 ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- BadOwner ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- StmtAuditOnCommit ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS
.- BadPublicObjects ... 1102000400 <= *All Rel* 12/07 11:48:45 PASS
.- BadSegFreelist ... 1102000400 <= *All Rel* 12/07 11:48:45 PASS
.- BadDepends ... 1102000400 <= *All Rel* 12/07 11:48:45 PASS
.- CheckDual ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS
.- ObjectNames ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS
.- BadCboHiLo ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS
.- ChkIotTs ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS
.- NoSegmentIndex ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS
.- BadNextObject ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS
.- DroppedROTS ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS
.- FilBlkZero ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS
.- DbmsSchemaCopy ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS
.- OrphanedObjError ... 1102000400 > 1102000000 12/07 11:48:47 PASS
.- ObjNotLob ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS
.- MaxControlfSeq ... 1102000400 <= *All Rel* 12/07 11:48:48 PASS
.- SegNotInDeferredStg ... 1102000400 > 1102000000 12/07 11:48:48 PASS
.- SystemNotRfile1 ... 1102000400 > 902000000 12/07 11:48:48 PASS
.- DictOwnNonDefaultSYSTEM ... 1102000400 <= *All Rel* 12/07 11:48:48 PASS
.- OrphanTrigger ... 1102000400 <= *All Rel* 12/07 11:48:48 PASS
.- ObjNotTrigger ... 1102000400 <= *All Rel* 12/07 11:48:48 PASS
---------------------------------------
07-DEC-2020 11:48:48 Elapsed: 8 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
PL/SQL procedure successfully completed.
Statement processed.
Complete output is in trace file:
/u01/app/oracle/diag/rdbms/killdb/killdb/trace/killdb_ora_32724_HCHECK.trc
操作有风险！上述操作均为个人测试，生产环境建议慎重。如果可能，还是重建库比较好。
发表回复