概述

数据库就跟我们人一样，如果你一直吃东西，但是却不排掉无用的垃圾，肯定是会越来越胖的。所以我们就需要定期去给数据库做瘦身。今天这部分内容主要介绍清理数据库审计表部分。

1、查看表空间整体情况

这里可以看到system表空间使用率达到99.99%，只要用dba_segments里面段做个排序就可以知道是aud$表影响的。

SELECT a.tablespace_name "tablespace_name",
 total/(1024 * 1024 * 1024) "all(G)",
 free/(1024 * 1024 * 1024) "free(G)",
 (total - free) / (1024 * 1024 * 1024) "used(G)",
 round((total - free)/total,4) * 100 "rate%"
 FROM (SELECT tablespace_name, SUM(bytes) free
 FROM dba_free_space
 GROUP BY tablespace_name) a,
 (SELECT tablespace_name, SUM(bytes) total
 FROM dba_data_files
 GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name;

2、查看sys.aud$的HWM线

2.1、创建HWM的存储过程

create or replace procedure show_space(p_segname in varchar2,
 p_owner in varchar2 default user,
 p_type in varchar2 default 'TABLE',
 p_partition in varchar2 default NULL) as
 l_total_blocks number;
 l_total_bytes number;
 l_unused_blocks number;
 l_unused_bytes number;
 l_LastUsedExtFileId number;
 l_LastUsedExtBlockId number;
 l_last_used_block number;
 procedure p(p_label in varchar2, p_num in number) is
 begin
 dbms_output.put_line(rpad(p_label, 40, '.') || p_num);
 end;
begin
 dbms_space.unused_space(segment_owner => p_owner,
 segment_name => p_segname,
 segment_type => p_type,
 partition_name => p_partition,
 total_blocks => l_total_blocks,
 total_bytes => l_total_bytes,
 unused_blocks => l_unused_blocks,
 unused_bytes => l_unused_bytes,
 last_used_extent_file_id => l_LastUsedExtFileId,
 last_used_extent_block_id => l_LastUsedExtBlockId,
 last_used_block => l_last_used_block);
 p('Total Blocks', l_total_blocks);
 p('Total Bytes', l_total_bytes);
 p('Unused Blocks', l_unused_blocks);
 p('Unused Bytes', l_unused_bytes);
 p('Last Used Ext FileId', l_LastUsedExtFileId);
 p('Last Used Ext BlockId', l_LastUsedExtBlockId);
 p('Last Used Block', l_last_used_block);
end;
/

2.2、查看HWM线

set serveroutput on
exec show_space(p_segname=>'AUD