A very interesting case I have faced in an Oracle database environment patched with 19.27 RU, I tried dropping an AWR snapshot and it failed as shown below:

BEGIN

    DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => ‘62386’, high_snap_id => ‘62387’);

   END;

   / 

ERROR at line 1:
ORA-13516: AWR Operation failed: AWR Schema is unavailable due maintenance
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 193
ORA-06512: at line 2

Solution:

Its unfortunately a bug !

// Index WRH$_SGASTAT_U in “WRH$_SGASTAT” table was in UNUSABLE state:

select * from dba_indexes where index_name=’WRH$_SGASTAT_U’;

// Drop the constraint:
alter table wrh$_sgastat drop constraint wrh$_sgastat_u;

// Turn off partition check:

alter session set events ‘14524 trace name context forever, level 1’;

// Add the constraint back:
alter table wrh$_sgastat add constraint wrh$_sgastat_u unique (dbid, snap_id, instance_number, name, pool, con_dbid, stattype) using index local tablespace SYSAUX;

// Turn on partition check:
alter session set events ‘14524 trace name context off’;

Then, Try dropping the AWR Snapshot again….should work.

BEGIN

   DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => ‘62386’, high_snap_id => ‘62387’);

   END;

   / 

PL/SQL procedure successfully completed.