« 传CNET将购CSDN和ChinaUnix Itpub离收购还有多远 | Blog首页 | 中文表名-导入导出-字符集 »
Statspack ORA-00001 错误的解决
作者：eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：https://www.eygle.com/archives/2005/10/statspack_unique_constraint_violated.html
有朋友遇到Statspack ORA-00001错误。链接：https://www.eygle.com/archives/2005/10/statspack_unique_constraint_violated.html
Errors in file /oracle/app/oracle/admin/shyz/bdump/shyz1_ora_2588734.trc:这个错误此前从未遇到，但是既然是主键冲突，那肯定是存在重复主键的数据。
ORA-12012: error on auto execute of job 328
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
Sun Oct 16 00:43:39 2005
肯定能暂时解决问题方法就是暂时禁用唯一约束检查:
ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE; |
然后观察数据来发现根本问题，最后彻底解决之。
到Metalink搜索了一下，发现存在一个相关Bug，Bug号为:2784796.
在设置了cursor_sharing为similar或者force之后，可能触发此Bug，导致主键冲突。
此bug据说在Oracle10g中已经修正。
历史上的今天...
>> 2019-10-16文章:
>> 2010-10-16文章:
>> 2009-10-16文章:
>> 2008-10-16文章:
>> 2007-10-16文章:
>> 2006-10-16文章:
>> 2004-10-16文章:
By eygle on 2005-10-16 23:08 | Comments (9) | Special | 477 |
还应当提醒他建一个同样结构的非维一性的索引, 否则snap多了, 要生成一个report很慢的.
我们已经这样做了好多次了.
恩，这个错误我是第一次见到，Metalink上好多说明，懒得看了。
你们出问题的系统是RAC环境么？
我们是在做statspack升级时发现的
我们一个crm库，非rac，
cursor_sharing string EXACT
一样还是遇到了同样的问题。
metalink有个workaround
重建view
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;
我们是由于v$sqlarea里存在4句相同的语句导致，第一次遇到这么奇怪的情况，sigh，浪费了一点eygle的电话费，呵呵。
当时正在参加F5的一个技术研讨会，啥都没帮上：）
如果是rac环境，提示ora-00001错误，但是在单机情况下不报错，会是什么原因呢？
我的环境 rac 9.2.0.5 一般在一台数据库重启后就会出现该错误 我一般把历史数据清掉就ok了 有时过几天不去管他 又好了 我怀疑跟内存数据有关系
$ sqlplus perfstat
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Feb 27 10:34:21 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.5.0 - Production
SQL> exec statspack.snap
BEGIN statspack.snap; END;
*
ERROR at 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
过几分钟
SQL> exec statspack.snap
PL/SQL procedure successfully completed.
oracle 10gR2 里面还有一个bug ，今天遇到，ft
Errors in file /opt/oracle/admin/forum/bdump/forum11_j000_17729.trc:
ORA-12012: error on auto execute of job 62
ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 5264
ORA-06512: at "PERFSTAT.STATSPACK", line 104
SQL> select index_name,column_name from dba_ind_columns where table_name = 'STATS$MUTEX_SLEEP';
INDEX_NAME
------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
STATS$MUTEX_SLEEP_PK
SNAP_ID
STATS$MUTEX_SLEEP_PK
DBID
STATS$MUTEX_SLEEP_PK
INSTANCE_NUMBER
INDEX_NAME
------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
STATS$MUTEX_SLEEP_PK
MUTEX_TYPE
STATS$MUTEX_SLEEP_PK
LOCATION
SQL> alter table STATS$MUTEX_SLEEP disable constraint STATS$MUTEX_SLEEP_pk;
Table altered.
SQL> select index_name,column_name from dba_ind_columns where table_name = 'STATS$MUTEX_SLEEP';
no rows selected
SQL> create index STATS$MUTEX_SLEEP_pk on STATS$MUTEX_SLEEP(snap_id,dbid,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION) tablespace users;
Index created.
SQL> select next_date from user_jobs;
NEXT_DATE
-------------------
2006-08-16 10:29:08
2006-08-16 12:00:00
SQL> exec dbms_job.run(62);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select count(*) from STATS$MUTEX_SLEEP;
COUNT(*)
----------
5
SQL> select * from STATS$MUTEX_SLEEP;
SNAP_ID DBID INSTANCE_NUMBER MUTEX_TYPE LOCATION
---------- ---------- --------------- -------------------------------- ----------------------------------------
SLEEPS WAIT_TIME
---------- ----------
5298 1834086304 1 Cursor Parent kkspsc0 [KKSPRTLOC26]
2 100
5298 1834086304 1 Cursor Pin kksLockDelete [KKSCHLPIN6]
1881 10384899
5298 1834086304 1 Cursor Pin kkslce [KKSCHLPIN2]
877 6442099
SNAP_ID DBID INSTANCE_NUMBER MUTEX_TYPE LOCATION
---------- ---------- --------------- -------------------------------- ----------------------------------------
SLEEPS WAIT_TIME
---------- ----------
5298 1834086304 1 Cursor Pin kksfbc [KKSCHLFSP2]
296 1699393
5298 1834086304 1 Cursor Pin kksfbc [KKSCHLFSP2]
5835 116531144
SQL>