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;
/

COLUMN min_id FORMAT 99999999999999999999
COLUMN max_id FORMAT 99999999999999999999

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

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

/*
To reduce a LOB segment in SYSAUX, first identify what owns it and whether it is a SecureFile or BasicFile LOB. The key difference is:

SecureFile LOBs can often be shrunk in place.
BasicFile LOBs cannot really be shrunk in place; you usually have to move/rebuild the parent table or move the LOB to another tablespace.
*/

-- Run this first:

SELECT owner, table_name, column_name, segment_name, tablespace_name, securefile
FROM dba_lobs
WHERE segment_name = 'SYS_LOB0000014105C00038$$';

SELECT segment_name, segment_type, bytes/1024/1024 AS mb
FROM dba_segments
WHERE segment_name = 'SYS_LOB0000014105C00038$$';


-- If it is a SecureFile LOB, try:
ALTER TABLE owner.table_name
  MODIFY LOB (column_name) (SHRINK SPACE);

-- If you want a more aggressive cleanup, you can use:
ALTER TABLE owner.table_name
  MODIFY LOB (column_name) (SHRINK SPACE CASCADE);

--If it is a BasicFile LOB, shrink is not supported. In that case the usual options are:
ALTER TABLE owner.table_name
  MOVE LOB (column_name)
  STORE AS (TABLESPACE <another_tablespace>);

/*
or rebuild/move the parent table so Oracle recreates the LOB segment smaller.

For SYS-owned objects in SYSAUX, the better fix is often not manual shrinking but deleting the underlying data that filled it, then letting Oracle reuse the space. Common examples are optimizer stats history or advisor data. If your segment is one of the WRI$_OPTSTAT... or related SYS tables, purge old stats first:
*/

EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);



select 'alter table '||l.owner||'.'||l.table_name||' modify lob ('||l.column_name||') (shrink space cascade); --'||s.bytes/1024/1024 as "Mb"
from dba_lobs l
  join dba_segments s on s.tablespace_name = l.tablespace_name
    and s.owner = l.owner
    and s.segment_type = 'LOBSEGMENT'
where l.owner = 'SYS'
and l.tablespace_name = 'SYSAUX'
--and l.table_name like 'WR%'
;