alter session set "_swrf_test_action" = 72;

exec dbms_workload_repository.create_snapshot;

select 'alter table ' || object_name || ' truncate partition ' ||

       subobject_name || ' update global indexes;'

  from dba_objects

 where object_name like 'WRH$%'

   and object_type = 'TABLE PARTITION'

   -- and object_name like '%WRH$_SYSTEM_EVENT%'

   and created < sysdate - 1

select t.owner,

       t.segment_name,

       t.partition_name,

       t.segment_type,

       t.blocks,

       round(t.BYTES / 1024 / 1024)

  from dba_segments t

 where t.tablespace_name = 'SYSAUX'

 order by t.BYTES desc

alter tablespace temp shrink space keep 100M;

alter tablespace temp shrink tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' keep 60M;

select a.file#,

       a.name,

       a.bytes / 1024 / 1024 CurrentMB,

       ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,

       (a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,

       'alter database datafile ''' || a.name || ''' resize ' ||

       ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD

  from v$datafile a,

       (select file_id, max(block_id + blocks - 1) HWM

          from dba_extents t

         where t.tablespace_name = 'IDX_TS1'

         group by file_id) b

 where a.file# = b.file_id

 order by 5