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

STATSPACK ORA-00001

Login as PERFSTAT and took a snapshot like the following statement. But it failed with ORA-00001.

This is a bug reported by Oracle and they suggest to replace the original view definition to workaround STATSPACK ORA-00001.

Solutions

Here are the steps.

1. Replace the trouble view

Login as SYS with SYSDBA privilege and then replace existing view STATS$V_$SQLXS.

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;
2. Truncate all data of STATSPACK

Login as PERFSTAT, do a STATSPACK truncate.

SQL> @?/rdbms/admin/sptrunc.sql
...
Table truncated.
...

If you can only use SYS, you may set the current schema to PERFSTAT.

SQL> alter session set current_schema = PERFSTAT;

Session altered.

SQL> @?/rdbms/admin/sptrunc.sql
...
3. Try to take a snapshot again
SQL> exec statspack.snap(i_snap_level=>5);

PL/SQL procedure successfully completed.

It passed.