Brotherxiao’s Home

Oracle 11gR2 New feature中引入了IGNORE_ROW_ON_DUPKEY_INDEX hint以解决insert …select 碰到的唯一性冲突问题，测试了一把，没有成功。看来新功能还有带检验。

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning and Real Application Testing options

SQL> show user
USER is "SYS"

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> select count(*) from t1;

COUNT(*)
----------
65690

SQL> insert into t1 select * from t1 where rownum<10000;

9999 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

COUNT(*)
----------
75689

SQL> create table t2 as select * from t1 where 1=0;

Table created.

SQL> alter table t2 add primary key(object_id);

Table altered.

SQL> insert into t2 select * from t1;
insert into t2 select * from t1
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C007007) violated

SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX */ into t2 select * from t1;
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX */ into t2 select * from t1
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C007007) violated

SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(t2,SYS_C007007) */ into t2
select * from t1;
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(t2,SYS_C007007) */ into t2 select * from t1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qerltcInsertSelectRop_bad_state],
[], [], [], [], [], [], [], [], [], [], []

SQL> insert into t2 select * from dba_objects;

65692 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(t2,SYS_C007007) */ into t2
select * from t1;
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(t2,SYS_C007007) */ into t2
select * from t1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qerltcInsertSelectRop_bad_state],

[], [], [], [], [], [], [], [], [], [], []

计划逐步将生产库升级到10g，今天在测试时发现一台机器上relink始终抱错，几经周折，终于找到了原因，记录之。

[oracle@xxx:/home/oracle]relink all
/opt/oracle/products/10.2.0/bin/genclntsh
ld: 0706-006 Cannot find or open library file: -l m
ld:open(): A file or directory in the path name does not exist.
ar: A file or directory in the path name does not exist.
ar: 0707-117 The fopen system call failed on file /opt/oracle//products/10.2.0/lib/shr.o.
mv: 0653-401 Cannot rename /opt/oracle/products/10.2.0/lib/shr.o to libclntsh.so.10.1:

最后发现是少装了一个文件集。

[oracle@xxx:/home/oracle] lslpp -L |grep bos.adt.libm

metalink有相关说明: 309668.1

今天发现oracle全文索引rebuild报错,网上看报有人能rebuild成功，不知是否版本问题。最后sync解决了问题。记录之

SQL> alter index SQL_CTX rebuild;
alter index SQL_CTX rebuild
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX SQL_CTX failed
DRG-10561: index SQL_CTX is not valid for requested operation

SQL> exec ctx_ddl.sync_index(’sql_ctx’, ‘20M’);

PL/SQL procedure successfully completed.

SQL> exec ctx_ddl.OPTIMIZE_INDEX(’sql_ctx’,'FAST’);

PL/SQL procedure successfully completed.