system01.dbf文件过大

用以下语句发现 AUD$数据表，占用了一部分空间。

select * from (
select t.tablespace_name,t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb
from dba_segments t
where t.segment_type='TABLE'
group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type
) t
order by t.mb desc

截断 AUD$审计表

truncate table sys.AUD$; 


select bytes/1024/1024/1024  from dba_segments where segment_name='AUD$';

关闭审计功能

alter system set audit_trail=none scope=spfile;

但是system01.dbf文件仍是 十几个GB。

resize system01.dbf

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 
         group by file_id) b 
where  a.file# = b.file_id(+) and (a.bytes - HWM *block_size)>0 order by 5;

执行resize功能

alter database datafile '/app/oracle/oradata/utf8/system01.dbf' resize 2048M;