[20191204]oracle能建立最大object_id是多少.txt

--//以前遇到一种情况,开发不了解oracle,不断建立表删除表(使用前缀+日期+顺序号方式建立表),本应该使用临时表的情况而使用普通表.
--//测试看看oracle能建立最大object_id是多少?

1.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立测试脚本:

$ cat aa.sql
create table tx (id number);
drop table tx purge ;

$ cat ab.sh
#! /bin/bash
while true
do
    sqlplus scott/book <<EOF > /dev/null
$(seq 1000 | xargs -I {} echo @aa.sql)
quit
EOF
done

--//关闭归档,避免产生大量归档日志.跑了好几个天,发现这样不可行.

3.不行,这样测试根本无法完成.
SCOTT@book> host cat aa.txt
select max(object_id) from dba_objects;
host sleep 10
select max(object_id) from dba_objects;

SCOTT@book> @ aa.txt

MAX(OBJECT_ID)
--------------
    1133502330

MAX(OBJECT_ID)
--------------
    1133503215

--//(1133503215 -  1133502330)/10 = 88.5,每秒建立89个对象.
--//最大0xffffffff = 4294967295 ,也就是2^32-1 = 4294967295.
--//(4294967295-1133503215)/89 = 35522068秒
--//35522068/86400 = 411.13504629629629629629天.如果以上操作还需要411天.
--//哎做事前没有认真分析估算,也可以看出即使这样操作在软件的生命周期内也很难使用完.

4.继续测试:
SYS@book> select max(object_id) from dba_objects;
MAX(OBJECT_ID)
--------------
    1133544904

SYS@book> select obj#,DATAOBJ#,name from (select * from sys.obj$ where dataobj# is not null order by DATAOBJ# desc ) where rownum<=4;
      OBJ#   DATAOBJ# NAME
---------- ---------- ----------------------------------------
         1 1133544905 _NEXT_OBJECT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~        
1133544904 1133544904 WRH$_FILESTATXS_PK
1133544902 1133544902 WRH$_FILESTATXS
1133544903 1133544896 WRH$_FILESTATXS_PK

--//是否可以人为修改下划线记录,来测试object_id最大是多少,千万不要在生产系统做这个测试!!
--//session 1:
SYS@book> update (select * from sys.obj$ where obj#=1) set DATAOBJ#=4294967295-2000;
1 row updated.

SYS@book> commit ;
Commit complete.

SYS@book> alter system flush shared_pool ;
System altered.

--//打开另外会话session 2:
SCOTT@book> drop table tx purge ;
Table dropped.

SCOTT@book> create table tx (id number);
create table tx (id number)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkdlron-max-objid], [4254950911], [4294965300], [], [], [], [], [], [], [], [], []

--//4294967295-2000 = 4294965295,感觉最大是4254950911.修改为4254950811测试看看.
--//session 1:
SYS@book> update (select * from sys.obj$ where obj#=1) set DATAOBJ#=4254950811;
1 row updated.

SYS@book> commit ;
Commit complete.

SYS@book> alter system flush shared_pool ;
System altered.

--//session 2:
SCOTT@book> create table tx (id number);
Table created.

SYS@book> select max(object_id) from dba_objects;
MAX(OBJECT_ID)
--------------
    4254950811

SYS@book> select obj#,DATAOBJ#,name from (select * from sys.obj$ where dataobj# is not null order by DATAOBJ# desc ) where rownum<=4;
      OBJ#   DATAOBJ# NAME
---------- ---------- ----------------------------------------
         1 4254950816 _NEXT_OBJECT
4254950811 4254950811 TX
1133544904 1133544904 WRH$_FILESTATXS_PK
1133544902 1133544902 WRH$_FILESTATXS
--//4254950816-4254950811 = 5.这样还能建立对象不是很多.这个数据库就废了.执行前面的ab.sh脚本:
--//不报错.ctrl+c中断.手工登录执行:

SCOTT@book> create table tx (id number);
create table tx (id number)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkdlron-max-objid], [4254950911], [4254950916], [], [], [], [], [], [], [], [], []

--//alert日志实际上报:
ORA-00600: internal error code, arguments: [kkdlron-max-objid], [4254950911], [4254950916], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
DDE: Problem Key 'ORA 600 [kkdlron-max-objid]' was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
--//被suppressed for up to 10 minutes.

SYS@book> select obj#,DATAOBJ#,name from (select * from sys.obj$ where dataobj# is not null order by DATAOBJ# desc ) where rownum<=4;
      OBJ#   DATAOBJ# NAME
---------- ---------- ----------------------------------------
         1 4254950911 _NEXT_OBJECT
1133544904 1133544904 WRH$_FILESTATXS_PK
1133544902 1133544902 WRH$_FILESTATXS
1133544903 1133544896 WRH$_FILESTATXS_PK

--//这个数据库就废了.基本不能再建立对象.4254950910 = 0xfd9d65fe,后面的数字是911,感觉oracle是否故意为之^_^.

SCOTT@book> create sequence xxx1;
create sequence xxx1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkdlron-max-objid], [4254950911], [4254950916], [], [], [], [], [], [], [], [], []

--//按照这个提示最大的DATAOBJ#是4254950910.不过按照上面的操作,这种情况发生非常极端,基本不可能出现.
--//我上网检索发现 : http://blog.sina.com.cn/s/blog_54ff313e0102ygky.html
--//https://blog.csdn.net/weixin_34185512/article/details/90571754
根据 notes 2137109 - Data Object ID limit，

The maximum number for data object id is limited to 4254950911. Data object id is a unique number. A create or truncate
operation increases the data object id by one.

On large BW systems with high data loads there is a small risk to reach the limitation of the maximum number for the
data object id.

If the maximum number for data object id is hit, the Oracle error

ORA-00600 [KKDLRON-MAX-OBJID] [4254950911]

will be reported. In case the error is reported, no new objects can be created and no truncate operations are possible.

解决办法： 只能重建。

In case the limit has been already hit and the database reports ORA-600 [KKDLRON-MAX-OBJID] [4254950911] the database
must be recreated and data exported from old database to the new created database.

To export the data the following options are available:
- use of the SAP R3load tool
- use of Oracle DataPump tool expdp/impdp. In case this option is choosen review SAP note 2141912

--//还真有人遇到这样的情况,这样的数据库一定很恐怖...^_^.