ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated 解决方案

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:143

今天在用户那用statspack收集信息做性能诊断时,job报以下错误
Sat Jul 11 21:11:26 2009
Errors in file /software/oracle/admin/sbzjdb/bdump/sbzjdb_j000_1151216.trc:
ORA-12012: error on auto execute of job 46
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
通过查metalink是一个bug,是cursor_sharing参数设置为force或simliar后才会出现的
bug号为2784796

可以有以下几种解决方法:
1.禁用主键
ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE;
然后建立同样结构的非唯一性索引

2.建立个视图
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
where ( plan_hash_value > 0
or executions > 0
or parse_calls > 0
or disk_reads > 0
or buffer_gets > 0
)
group by hash_value, address;
create or replace public synonym STATS$V$SQLXS for STATS$V_$SQLXS;

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】