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

Note. 393300.1 Ora-00001: Unique Constraint (Perfstat.Stats$Sql_summary_pk)Violated
- Error ORA-00001 running the PERFSTAT.STATS when applying the OCS patch.
Verified the issue by the trace generated in the sqlplus that shows the error below:

 execute 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


This is issue for PERFSTAT.STATS in database and 9.2.



To implement the solution, please execute the following steps:
1. Please run the following sql statements: (Please be sure the 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 where status =’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;


<<NOTE: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



  1. Very good information, thanks.

  2. You are welcome !!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: