Statspack ORA-00001 unique constraint violated错误的解决

我这里分两部分，这个问题在网上查到一个解决方案，从mos里又看到更加全的。

============================转载部分===========================================

ORA-00001:uniqueconstraint(PERFSTAT.STATS$SQL_SUMMARY_PK)violated

ORA-06512:at"PERFSTAT.STATSPACK",line1361
ORA-06512:at"PERFSTAT.STATSPACK",line2471
ORA-06512:at"PERFSTAT.STATSPACK",line91
ORA-06512:atline1
SunOct1600:43:392005


这个错误此前从未遇到，但是既然是主键冲突，那肯定是存在重复主键的数据。

肯定能暂时解决问题方法就是暂时禁用唯一约束检查:
ALTERTABLEPERFSTAT.STATS$SQL_SUMMARY
MODIFYCONSTRAINTSTATS$SQL_SUMMARY_PKDISABLENOVALIDATE;

然后观察数据来发现根本问题，最后彻底解决之。

到Metalink搜索了一下，发现存在一个相关Bug，Bug号为:2784796.
在设置了cursor_sharing为similar或者force之后，可能触发此Bug，导致主键冲突。

此bug据说在Oracle10g中已经修正。

原链接：http://www.eygle.com/archives/2005/10/statspack_unique_constraint_violated.html

=======================================原创============================================

execute statspack.snap

或
BEGIN statspack.snap; END;

执行创建snap的时候报错

ERRORat line 1:
ORA-00001 : unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512 : at "PERFSTAT.STATSPACK",line 1361
ORA-06512 : at "PERFSTAT.STATSPACK", line 2442
ORA-06512 : at "PERFSTAT.STATSPACK", line 91
ORA-06512 : at line 1

从MOS上看，这个bug在9.0.1.4 到 9.2的一个bug

解决方法如下：

sqlplus " / as sysdba"
SQL> spool run_statspack.txt
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
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;
SQL> select owner,object_name from dba_objects wherestatus ='INVALID';
SQL> @?/rdbms/admin/utlrp
SQL> select owner,object_name from dba_objects where status ='INVALID';
SQL> conn perfstat/perfstat_password
SQL> execute statspack.snap(i_snap_level=>5);
SQL> spool off;

metalink原文

Subject: Ora-00001: Unique Constraint (Perfstat.Stats$Sql_summary_pk) Violated Doc ID: Note:393300.1 Type: PROBLEM Last Revision Date: 14-NOV-2007 Status: PUBLISHED In this Document [size=-1]Symptoms Cause Solution References @ (AuthWiz 2.5.2) Created fromSR 16637284.6withODM @Click hereto edit in wizard. Applies to:Oracle Server - Enterprise Edition - Version: 9.2.0.6 This problem can occur on any platform. Symptoms- Error ORA-00001 running the PERFSTAT.STATS when applying the OCS 9.0.4.2.7 patch. . Verified the issue by the tracegenerated in the sqlplus that shows the error below: execute statspack.snap BEGIN statspack.snap; END; * ERRORat line 1: ORA-00001 : unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated ORA-06512 : at "PERFSTAT.STATSPACK",line 1361 ORA-06512 : at "PERFSTAT.STATSPACK", line 2442 ORA-06512 : at "PERFSTAT.STATSPACK", line 91 ORA-06512 : at line 1 . CauseThis is issue for PERFSTAT.STATS in database 9.0.1.4 and 9.2. Database Bug.2784796 - ORA-00001 UNIQUE CONSTRAINT (PERFSTAT.STATS$SQL_SUMMARY_PK) VIOLATED. . SolutionTo implement the solution, please execute the following steps: 1. Please run the following sql statements: (Please be surethe view STATS$V_$SQLXS created successfully in the sys schema) sqlplus " / as sysdba" SQL> spool run_statspack.txt 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 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; SQL> select owner,object_name from dba_objects wherestatus ='INVALID'; SQL> @?/rdbms/admin/utlrp SQL> select owner,object_name from dba_objects where status ='INVALID'; SQL> conn perfstat/perfstat_password SQL> execute statspack.snap(i_snap_level=>5); SQL> spool off; ReferencesNote 149113.1- Installing and Configuring StatsPack Package Note 149121.1- Gathering a StatsPack snapshot Note 267244.1- Cannot Run Statspack.Snap Ora-00001 Note 94224.1- FAQ- Statspack Complete Reference Bug 2784796- ORA-00001:UNIQUE CONSTRAINT (PERFSTAT.STATS$SQL_SUMMARY_PK) VIOLATED Errors

====================================================

附原链接则可转载，否则追求法律责任！

Email： orahank.dai@gmail.com

QQ： 88285879

新浪微博：http://weibo.com/u/1939547104

更新博客后，感觉比较实用和经典的，会精简化写入微博，并附相应链接，希望给各位带来帮助，哈哈！