出现如下错误(ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated)
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.6.0 - Production ORACLE_HOME = /oracle9/app/product/9.2.0 System name: AIX Node name: zwq_bill_1 Release: 1 Version: 6 Machine: 00F64FF34C00 Instance name: bill1 Redo thread mounted by this instance: 1 Oracle process number: 30 Unix process pid: 46531060, image: oracle@zwq_bill_1 (J000) *** SESSION ID:(218.47085) 2012-04-02 19:30:45.561 *** 2012-04-02 19:30:45.561 ORA-12012: error on auto execute of job 1 ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated ORA-06512: at "PERFSTAT.STATSPACK", line 1361 ORA-06512: at "PERFSTAT.STATSPACK", line 2471 ORA-06512: at "PERFSTAT.STATSPACK", line 91 ORA-06512: at line 1
这个是oracle的一个Bug 2784796,提供解决方法有
1.run the statspack at level 0
2.restart the instance
3.set cursor sharing to exact (probably not feasible)
4.禁用主键，创建合适非唯一index
ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE;
5.修改STATS$V_$SQLXS视图
分析思路如下:
1)根据主键冲突找到主键包含列(spctab.sql)
create table STATS$SQL_SUMMARY
(snap_id number(6) not null
,dbid number not null
,instance_number number not null
,text_subset varchar2(31) not null
,sql_text varchar2(1000)
,sharable_mem number
,sorts number
,module varchar2(64)
,loaded_versions number
,fetches number
,executions number
,loads number
,invalidations number
,parse_calls number
,disk_reads number
,buffer_gets number
,rows_processed number
,command_type number
,address raw(8)
,hash_value number
,version_count number
,cpu_time number
,elapsed_time number
,outline_sid number
,outline_category varchar2(64)
,child_latch number
--注意下面5列构成主键
,constraint STATS$SQL_SUMMARY_PK primary key
(snap_id, dbid, instance_number, hash_value, text_subset)
using index tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0)
,constraint STATS$SQL_SUMMARY_FK foreign key (snap_id, dbid, instance_number)
references STATS$SNAPSHOT on delete cascade
)tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
2)找到该表插入数据(spcpkg.sql)
insert into stats$sql_summary
( snap_id
, dbid
, instance_number
, text_subset
, sharable_mem
, sorts
, module
, loaded_versions
, fetches
, executions
, loads
, invalidations
, parse_calls
, disk_reads
, buffer_gets
, rows_processed
, command_type
, address
, hash_value
, version_count
, cpu_time
, elapsed_time
, outline_sid
, outline_category
, child_latch
)
select l_snap_id
, p_dbid
, p_instance_number
, substrb(sql_text,1,31)
, sharable_mem
, sorts
, module
, loaded_versions
, fetches
, executions
, loads
, invalidations
, parse_calls
, disk_reads
, buffer_gets
, rows_processed
, command_type
, address
, hash_value
, version_count
, cpu_time
, elapsed_time
, outline_sid
, outline_category
, child_latch
from stats$v$sqlxs
where is_obsolete = 'N'
and ( buffer_gets > l_buffer_gets_th
or disk_reads > l_disk_reads_th
or parse_calls > l_parse_calls_th
or executions > l_executions_th
or sharable_mem > l_sharable_mem_th
or version_count > l_version_count_th
);
3)找出stats$v$sqlxs对象(spcusr.sql)
create or replace view STATS$V_$SQLXS as
select max(sql_text) sql_text
, sum(sharable_mem) sharable_mem
, sum(sorts) sorts
, min(module) module
, sum(loaded_versions) loaded_versions
, sum(fetches) fetches
, sum(executions) executions
, sum(loads) loads
, sum(invalidations) invalidations
, sum(parse_calls) parse_calls
, sum(disk_reads) disk_reads
, sum(buffer_gets) buffer_gets
, sum(rows_processed) rows_processed
, max(command_type) command_type
, address address
, hash_value hash_value
, count(1) version_count
, sum(cpu_time) cpu_time
, sum(elapsed_time) elapsed_time
, max(outline_sid) outline_sid
, max(outline_category) outline_category
, max(is_obsolete) is_obsolete
, max(child_latch) child_latch
from v$sql
group by hash_value, address;
create or replace public synonym STATS$V$SQLXS for STATS$V_$SQLXS;
4)通过这里可以看出，要是的STATS$SQL_SUMMARY主键不重复，只要是的STATS$V_$SQLXS查询出来的记录唯一，所以解决方案就是在STATS$V_$SQLXS视图中增加下列条件，确保查询出来的记录唯一，从而不会发生主键冲突
where ( plan_hash_value > 0 or executions > 0 or parse_calls > 0 or disk_reads > 0 or buffer_gets > 0 )
该bug在10g中修复，对于不能及时升级的数据库，建议采用第五种方法解决问题，比较治标治本，对业务基本上无影响
