Statspack snap failed with ORA-00001: unique constraint

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 4349
ORA-06512: at "PERFSTAT.STATSPACK", line 5697
ORA-06512: at "PERFSTAT.STATSPACK", line 105
ORA-06512: at line 1


#

SQL> select object_name,object_type from dba_objects where object_name like 'STATS$SQL_SUMMARY%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
STATS$SQL_SUMMARY              SYNONYM
STATS$SQL_SUMMARY              TABLE
STATS$SQL_SUMMARY_PK           INDEX


SQL> select owner,CONSTRAINT_NAME,TABLE_NAME,STATUS from dba_constraints  where table_name='STATS$SQL_SUMMARY';

OWNER                          CONSTRAINT_NAME                TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
PERFSTAT                       SYS_C002724217                 STATS$SQL_SUMMARY              ENABLED
PERFSTAT                       SYS_C002724216                 STATS$SQL_SUMMARY              ENABLED
PERFSTAT                       SYS_C002724215                 STATS$SQL_SUMMARY              ENABLED
PERFSTAT                       SYS_C002724214                 STATS$SQL_SUMMARY              ENABLED
PERFSTAT                       SYS_C002724213                 STATS$SQL_SUMMARY              ENABLED
PERFSTAT                       STATS$SQL_SUMMARY_FK           STATS$SQL_SUMMARY              ENABLED
PERFSTAT                       STATS$SQL_SUMMARY_PK           STATS$SQL_SUMMARY              ENABLED

7 rows selected.



####Disable the STATS$SQL_SUMMARY_PK constraint:
SQL> ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE;

Table altered.

####Collect the DDL of associated index.

set long 99999
select DBMS_METADATA.GET_DDL('INDEX','STATS$SQL_SUMMARY_PK','PERFSTAT') FROM DUAL;


CREATE UNIQUE INDEX "PERFSTAT"."STATS$SQL_SUMMARY_PK" ON "PERFSTAT"."STATS$SQL_SUMMARY" ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "OLD_HASH_VALUE", "TEXT_SUBSET")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PERFSTAT";



###Re-Created the Index (without unique) 

CREATE INDEX "PERFSTAT"."STATS$SQL_SUMMARY_PK" ON "PERFSTAT"."STATS$SQL_SUMMARY" ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "OLD_HASH_VALUE", "TEXT_SUBSET")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PERFSTAT";

##Run the stats 

SQL> exec dbms_stats.gather_schema_stats('PERFSTAT',estimate_percent => 100,cascade => true , no_invalidate => false);

or

SQL> exec dbms_stats.gather_table_stats('PERFSTAT','STATS$SQL_SUMMARY',estimate_percent => 100,cascade => true , no_invalidate => false);

# re-run the statspack
SQL> conn PERFSTAT/PERFSTAT

Connected.

SQL> EXECUTE statspack.snap;



2. 

SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;

no rows selected

SQL> 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 2471
ORA-06512: at "PERFSTAT.STATSPACK", line 91
ORA-06512: at line 1

As per Doc ID 393300.1,



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
2 3 4 5 6 7 , sum(fetches) fetches
, sum(executions) executions
, sum(loads) loads
, sum(invalidations) invalidations
, sum(parse_calls) parse_calls
, sum(disk_reads) disk_reads
8 , sum(buffer_gets) buffer_gets
9 10 , sum(rows_processed) rows_processed
, max(command_type) command_type
11 12 13 14 15 16 , address address
, hash_value hash_value
17 18 , count(1) version_count
, sum(cpu_time) cpu_time
, sum(elapsed_time) elapsed_time
19 , max(outline_sid) outline_sid
, max(outline_category) outline_category
, max(is_obsolete) is_obsolete
, max(child_latch) child_latch
20 21 from v$sql
where ( plan_hash_value > 0
or executions > 0
22 23 or parse_calls > 0
or disk_reads > 0
24 25 26 27 28 29 30 or buffer_gets > 0)
group by hash_value, address; 31

View created.

SQL> @?/rdbms/admin/utlrp

PL/SQL procedure successfully completed.


Table created.


Table created.


Table created.


Index created.


Table created.


Table created.


View created.


View created.


Package created.

No errors.

Package body created.

No errors.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.



JOB PRIV_USER SCHEMA_USER NEXT_DATE INTERVAL FAILURES B WHAT
---------- -------------------- -------------------- -------------------- -------------------- ---------- - ------------------------------------------------------------
146 PERFSTAT PERFSTAT 02-JAN-2021 06:00:00 trunc(SYSDATE+1/24,' 12 N statspack.snap;
HH')

149 PERFSTAT PERFSTAT 03-JAN-2021 00:00:00 trunc(sysdate+1) N statspack.purge(i_num_days=>;90,i_extended_purge=>;TRUE);

SQL> EXECUTE statspack.snap;

PL/SQL procedure successfully completed.

SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;

NAME SNAP_ID Date/Time
--------- ---------- -------------------
RMS 14 02.01.2021:05:08:33

###Reference Note##

Cannot Run Statspack.Snap ORA-00001 (Doc ID 267244.1)

ORA-1 on PERFSTAT.STATS$MUTEX_SLEEP_PK when executing statspack.snap (Doc ID 382993.1)

Ora-00001: Unique Constraint (Perfstat.Stats$Sql_summary_pk) Violated (Doc ID 393300.1)