Hint IGNORE_ROW_ON_DUPKEY_INDEX 引发的ora-600错误

看到 http://yangtingkun.itpub.net/post/468/492325 里 描述 当使用此hint时 批量插入的行数超过255行就会引发错误：ORA-00600: 内部错误代码, 参数; [qerltcInsertSelectRop_bad_state], [], [], [], [], [], [], [], [], [], [], []
在本地环境试了一下确实如此
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (cm_user,PK_USER)*/ INTO cm_user SELECT * FROM cm_user@dev_wos.domain WHERE ROWNUM<77;
 
INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (cm_user,PK_USER)*/ INTO cm_user SELECT * FROM cm_user@dev_wos.domain WHERE ROWNUM<77
 
ORA-00600: internal error code, arguments: [qerltcInsertSelectRop_bad_state], [], [], [], [], [], [], [], [], [], [], []
 
SQL> rollback
  2  /
 
Rollback complete
 
Executed in 0.047 seconds
 
SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (cm_user,PK_USER)*/ INTO cm_user SELECT * FROM cm_user@dev_wos.domain WHERE ROWNUM<76;
 
75 rows inserted
 
Executed in 0.016 seconds
 
SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (cm_user,PK_USER)*/ INTO cm_user SELECT * FROM cm_user@dev_wos.domain WHERE ROWNUM<76;
 
0 rows inserted
 
Executed in 0.031 seconds
插入总行数超过75行报错(初始行数100)

SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (cm_location,PK_LOCATION)*/ INTO cm_location SELECT * FROM cm_location@dev_wos.domain WHERE ROWNUM<100;
 
99 rows inserted
 
Executed in 0.266 seconds
 
SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (cm_location,PK_LOCATION)*/ INTO cm_location SELECT * FROM cm_location@dev_wos.domain WHERE ROWNUM<116;
 
INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (cm_location,PK_LOCATION)*/ INTO cm_location SELECT * FROM cm_location@dev_wos.domain WHERE ROWNUM<116
 
ORA-00600: internal error code, arguments: [qerltcInsertSelectRop_bad_state], [], [], [], [], [], [], [], [], [], [], []
插入总行数超过115行报错(初始行数99)

看来不同的表的批量处理上限还不一样

又在 11.2.0.2.0上试了一下 没有发现这个错误，看来是修复了

SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (cm_location,PK_LOCATION)*/ INTO cm_location SELECT * FROM cm_location WHERE ROWNUM<100;
 
0 rows inserted
 
Executed in 0.11 seconds
 
SQL> rollback
  2  /
 
Rollback complete
 
Executed in 0 seconds
 
SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (cm_location,PK_LOCATION)*/ INTO cm_location SELECT * FROM cm_location WHERE ROWNUM<1000;
 
0 rows inserted
 
Executed in 0.297 seconds
 
SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (cm_location,PK_LOCATION)*/ INTO cm_location SELECT * FROM cm_location WHERE ROWNUM<10000;
 
0 rows inserted
 
Executed in 4.235 seconds
 
SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (cm_location,PK_LOCATION)*/ INTO cm_location SELECT * FROM cm_location ;
 
0 rows inserted
 
Executed in 43.562 seconds

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24022604/viewspace-711470/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24022604/viewspace-711470/