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

[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

--//還真有人遇到這樣的情況,這樣的資料庫一定很恐怖...^_^.