SYSTEM表空间过大问题

巡检发现system表空间使用率过高

select a.tablespace_name,total,free,total-free used, (total-free)/total from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by (total-free)/total ;

TABLESPACE_NAME TOTAL FREE USED FREE/TOTAL
------------------------------ ---------- ---------- ---------- ----------
A11_ 28000 9823.0625 18176.9375 .350823661
A11_ 142512 29388.8125 113123.188 .206219915
SYSAUX 5120 3242.8125 1877.1875 .633361816
SYSTEM 5120 767.25 4352.75 .149853516
UNDOTBS1 1595 1021.5 573.5 .640438871
UNDOTBS2 1300 560.25 739.75 .430961538
USERS 5 3.5625 1.4375 .7125

7 rows selected.

 

检查system表空间中占用空间最大对象

  SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSTEM' GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;

aud$为审计功能

导出aud$的数据备查后，

 truncate table aud$;

 

==============================================================================================================================================================

以下是第二个系统的情况：

select a.tablespace_name,total,free,total-free used, (total-free)/total from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by (total-free)/total ;

 

查看最大对象：

 

导出数据，并截断 aud$后

 

system问题以解决，仍需继续观察users表空间，是否在继续增长。该系统未将用户数据存放为单独表空间。

 

===================================================================================================================================================================

一下是第三套系统情况：

 

SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSAUX' GROUP BY SEGMENT_NAME ORDER BY 2 DESC) ;

 

查看表空间只有3GB不算太大，因此采取扩大sysaux表空间对应数据文件为10G，并继续观察空间增长的方式解决。 

alter database datafile 'G:\APP\ADMINISTRATOR\ORADATA\THA11PRODGT\SYSAUX01.DBF' resize 10200 m;

 database datafile 'G:\APP\ADMINISTRATOR\ORADATA\THA11PRODGT\SYSAUX01.DBF'已变更。

 处理后表空间占用量：

后续该系统扔应关注user表空间是否继续扩大问题。