set linesize 120
set pagesize 100
col ash form a30
col retention form a30
col snap form a30
 
COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40
 
SELECT  occupant_name "Item",
space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
WHERE occupant_name = 'SM/AWR'
ORDER BY 1
/
 
SELECT 'alter index '||segment_name||' rebuild online parallel (degree 4);'
FROM dba_segments
WHERE tablespace_name= 'SYSAUX'
AND segment_name LIKE 'WRH$_%'
AND segment_type='INDEX'
ORDER BY segment_name; 

/

--- Move SYSAUX WRH$_% tables to reclaim space
SELECT 'exec dbms_pdb.exec_as_oracle_script(''alter table '||segment_name||' move tablespace sysaux'');'
FROM dba_segments
WHERE tablespace_name= 'SYSAUX'
AND segment_name LIKE 'WRH$_%'
AND segment_type = 'TABLE'  
ORDER BY segment_name;

/

select table_name, count(*)
from dba_tab_partitions
where table_name like 'WRH$%'
and table_owner = 'SYS'
group by table_name
order by 1;

/

begin
 execute immediate 'alter session set "_swrf_test_action" = 72';
end;
/

SELECT MIN(sample_id), MAX(sample_id)
FROM sys.wrh$_active_session_history;
-- 80381470007	81701088731

EXEC dbms_workload_repository.drop_snapshot_range(low_snap_id=>80381470007, high_snap_id=>81701088731);
/