Andrew Fraser DBA

Sysaux Space Delete Orphaned Rows

SELECT s.owner
     , s.segment_name
     , ROUND ( SUM ( s.bytes ) /1024/1024/1024 , 2 ) AS gb
     , COUNT(*)
     , s.tablespace_name
  FROM dba_segments s
 WHERE s.tablespace_name = 'SYSAUX'
 GROUP BY s.owner , s.segment_name , s.tablespace_name
 ORDER BY SUM ( s.bytes ) DESC , 1 , 2
;
set timing on
DELETE FROM sys.wrh$_latch t
 WHERE NOT EXISTS (
       SELECT NULL
         FROM sys.wrm$_snapshot h
        WHERE h.snap_id = t.snap_id
          AND h.dbid = ( SELECT d.dbid FROM v$database d )
          AND h.dbid = t.dbid
          AND h.instance_number = t.instance_number
       )
;
DELETE FROM sys.wrh$_sysStat t
 WHERE NOT EXISTS (
       SELECT NULL
         FROM sys.wrm$_snapshot h
        WHERE h.snap_id = t.snap_id
          AND h.dbid = ( SELECT d.dbid FROM v$database d )
          AND h.dbid = t.dbid
          AND h.instance_number = t.instance_number
       )
;
DELETE FROM sys.wrh$_parameter t
 WHERE NOT EXISTS (
       SELECT NULL
         FROM sys.wrm$_snapshot h
        WHERE h.snap_id = t.snap_id
          AND h.dbid = ( SELECT d.dbid FROM v$database d )
          AND h.dbid = t.dbid
          AND h.instance_number = t.instance_number
       )
;
DELETE FROM sys.wrh$_event_histogram t
 WHERE NOT EXISTS (
       SELECT NULL
         FROM sys.wrm$_snapshot h
        WHERE h.snap_id = t.snap_id
          AND h.dbid = ( SELECT d.dbid FROM v$database d )
          AND h.dbid = t.dbid
          AND h.instance_number = t.instance_number
       )
;
DELETE FROM sys.wrh$_sysmetric_summary t
 WHERE NOT EXISTS (
       SELECT NULL
         FROM sys.wrm$_snapshot h
        WHERE h.snap_id = t.snap_id
          AND h.dbid = ( SELECT d.dbid FROM v$database d )
          AND h.dbid = t.dbid
          AND h.instance_number = t.instance_number
       )
;
DELETE FROM sys.wrh$_sysmetric_history t
 WHERE NOT EXISTS (
       SELECT NULL
         FROM sys.wrm$_snapshot h
        WHERE h.snap_id = t.snap_id
          AND h.dbid = ( SELECT d.dbid FROM v$database d )
          AND h.dbid = t.dbid
          AND h.instance_number = t.instance_number
       )
;
COMMIT ;
ALTER TABLE sys.wrh$_latch SHRINK SPACE CASCADE ;
ALTER TABLE sys.wrh$_sysstat SHRINK SPACE CASCADE ;
ALTER TABLE sys.wrh$_parameter SHRINK SPACE CASCADE ;
ALTER TABLE sys.wrh$_event_histogram SHRINK SPACE CASCADE ;
ALTER TABLE sys.wrh$_sysmetric_history SHRINK SPACE CASCADE ;
-- "ORA-10636: ROW MOVEMENT is not enabled" for below
ALTER TABLE sys.wrh$_sysmetric_summary SHRINK SPACE CASCADE ;
set timing off