Skip to content

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


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

SQL> exec statspack.snap(i_snap_level=>5);
BEGIN statspack.snap(i_snap_level=>5); 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 a bug reported by Oracle and they suggest to replace the original view definition to workaround STATSPACK ORA-00001.


Here are the steps.

  1. Replace the trouble view
  2. 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;
  3. Truncate all data of STATSPACK
  4. 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
  5. Try to take a snapshot again
  6. SQL> exec statspack.snap(i_snap_level=>5);

    PL/SQL procedure successfully completed.

It passed.

Leave a Reply

Your email address will not be published. Required fields are marked *