ORA-00001: unique constraint

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

感謝參考連結:
http://www.eygle.com/archives/2005/10/statspack_unique_constraint_violated.html


Thu Dec 22 19:30:23 2016
Errors in file /oracle/admin/NBSPRD3/bdump/nbsprd3_j001_17892114.trc:
ORA-12012: error on auto execute of job 243
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


Metalink 相關 Bug num :2784796.
在 cursor_sharing similar 或 force 後，可能發此Bug。

參考作法  :重建 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;

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

oracle 10gR2 裡還有 bug :

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

create or replace public synonym STATS$V$SQLXS for STATS$V_$SQLXS;