Purging oracle awr details

Reduce the space of the table by first purging old data using dbms_workload_repository.purge_sql_details.

This procedure purges SQL details, specifically rows from WRH$_SQLTEXT and WRH$_SQL_PLAN that do not have corresponding rows (DBID, SQL_ID) in WRH$_SQLSTAT

Check row count and size first.

select count(*) from WRH$_SQL_PLAN ;

select sum(bytes) from dba_segments where segment_name ='WRH$_SQL_PLAN';

Run the purge.

select distinct(dbid) from wrm$_snapshot;

exec dbms_workload_repository.purge_sql_details(&dbid);

Move the table out of syntax and back in, and rebuild the index.

alter table WRH$_SQL_PLAN move tablespace users parallel 10;

alter table WRH$_SQL_PLAN move tablespace sysaux parallel 10;

alter index SYS.WRH$_SQL_PLAN_PK rebuild online;

In addition, you can shrink the space allocated to the table with the following steps.

Shrink the space after purging the rows.

ALTER TABLE WRH$_SQL_PLAN enable row movement;

ALTER TABLE WRH$_SQL_PLAN SHRINK SPACE CASCADE;

ALTER TABLE WRH$_SQL_PLAN disable row movement;