oracle undo收缩,清理SYSAUX表空间与收缩undo、redo表空间及找出可收缩的文件

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