Oracle表空间使用率过大问题处理方法

一、 查看Oracle数据库表空间使用情况

SELECT a.tablespace_name "表空间名",  
       total "表空间大小",  
       free "表空间剩余大小",  
       (total - free) "表空间使用大小",  
       total/(1024*1024*1024) "表空间大小(G)",  
       free/(1024*1024*1024) "表空间剩余大小(G)",  
       (total - free)/(1024*1024*1024) "表空间使用大小(G)",  
       ROUND((total - free) / total, 4) * 100 "使用率 %"  
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;

二、扩大表空间的四种方法

--给表空间增加数据文件
ALTER  TABLESPACE  app_data  ADD  DATAFILE  'D:\dbfile\app_01.dbf'  SIZE  1G;


--新增数据文件，并且允许数据文件自动增长
ALTER  TABLESPACE  app_data  ADD  DATAFILE  'D:\dbfile\app_02.dbf'  SIZE  500M
AUTOEXTEND  ON  NEXT  50M  MAXSIZE  5G;


--允许已存在的数据文件自动增长
ALTER  DATABASE  DATAFILE  'D:\dbfile\app_01.dbf'
AUTOEXTEND  ON  NEXT  50M  MAXSIZE  UNLIMITED;


--手工改变已存在数据文件的大小
ALTER  DATABASE  DATAFILE  'D:\dbfile\app_01.dbf' RESIZE 500M;

三、系统表空间过大的情况 

1）system表空间过大（使用率95%以上）

    a）检查aud$表大小

--查看数据库表大小SQL
select bytes,owner,segment_name 
from dba_segments 
where segment_type='TABLE' order by bytes desc;

--查看aud$表大小SQL
select bytes,owner,segment_name 
from dba_segments 
where segment_type='TABLE' and segment_name = 'AUD$';

    b） 如果aud$过大，清理之

        导出aud$表后，用truncate清理

    c） 如果出现aud$表为空,system表空间使用率照样达到99%，建议增加数据文件，如下：

alter tablespace system add datafile 'D:\dbfile\SYSTEM_02.DBF' size 50M autoextend on

select * from dba_data_files;

2）sysaux表空间（使用率95%以上）

a） 修改统计信息保存时间

select dbms_stats.get_stats_history_retention from dual;  --检查统计信息保存时间（默认应该是31天）

exec dbms_stats.alter_stats_history_retention(7);  --如果31天将其改为7天

select dbms_stats.get_stats_history_retention from dual;  --验证是否修改成功

b） 删除AWR报告快照

        批注：Oracle 10g中快照会保留7天，11g的快照保留8天，超出会自动删除。AWR快照可以从其他数据库导入，而这部分数据会保存时间极长。有时候也会遇到自动快照不能自动收集，而手工创建的快照又可以成功，对于这种情况就需要把以前的快照清理掉。

            删除AWR有两种方式进行删除：dbms_workload_repository，dbms_swrf_internal。dbms_workload_repository可以删除本地和其他数据库的快照，可以选择不同的快照来进行删除；dbms_swrf_internal只能对其他数据库的快照来进行操作，会把所有的快照直接干掉。

        使用dbms_workload_repository包删除

 select dbid, retention from dba_hist_wr_control;
 select min(snap_id), max(snap_id)from dba_hist_snapshot where dbid = '得到的dbid值';
 exec dbms_workload_repository.drop_snapshot_range('得到的min(snap_id)值','得到的max(snap_id)值','得到的dbid值');
 select * from dba_hist_snapshot where dbid = '得到的dbid值';

        使用dbms_swrf_internal包删除

 select dbid, retention from dba_hist_wr_control;
 select min(snap_id), max(snap_id)from dba_hist_snapshot where dbid = '得到的dbid值';
 exec dbms_swrf_internal.unregister_database('得到的dbid值');
 select * from dba_hist_snapshot where dbid = '得到的dbid值';