ITPub博客

首页 > 数据库 > Oracle > SYSAUX表空间使用率高问题处理

SYSAUX表空间使用率高问题处理

Oracle 作者:studywell 时间:2016-01-26 13:03:15 0 删除 编辑
---SYSAUX表空间使用率高问题处理

--参考:http://ylw6006.blog.51cto.com/470441/1135593/

--一:使用下列语句查询表空间使用率
SELECT
d.tablespace_name "表空间",
d.status "状  态",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "总容量(MB)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') "已使用(MB)",
TO_CHAR(NVL(f.bytes, 0)/1024/1024,'99999999.999') "未使用(MB)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "使用率(%)"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY')
UNION ALL SELECT d.tablespace_name "表空间",d.status "状  态",  TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "总容量(MB)",
TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99999999.999')  "已使用(MB)",
TO_CHAR(Nvl(a.bytes-NVL(t.bytes, 0),0)/1024/1024,'99999999.999') "未使用(MB)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "使用率(%)" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes)
bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool
group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';


--二:查询SYSAUX表空间内各个分类项目占存储空间的比重,很明显可以看出来AWR快照占用了2G左右的空间,统计信息为149M左右,同时数据库关闭了审计audit_trail,所以审计表aud$不占空间    
 SELECT occupant_name "Item",
           space_usage_kbytes / 1048576 "Space Used (GB)",
           schema_name "Schema",
           move_procedure "Move Procedure"
      FROM v$sysaux_occupants
 order by 2 desc;
 
 select *   FROM v$sysaux_occupants s order by s.SPACE_USAGE_KBYTES desc;
     
--三:修改统计信息的保持时间,默认为31天,这里修改为7天,过期的统计信息会自动被删除
    select dbms_stats.get_stats_history_retention from dual;
    exec dbms_stats.alter_stats_history_retention(7);       
    select dbms_stats.get_stats_history_retention from dual;
    
   -- 查看系统的当前的MOVING_WINDOW_SIZE
    select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;
    exec dbms_workload_repository.modify_baseline_window_size(2);
    
 
--四:修改AWR快照的保存时间为3天(3*24*60),每小时收集一次,也可以通过EM界面查看和修改
    --当前默认保留时间
    select * from dba_hist_wr_control;

  SQL> begin
             dbms_workload_repository.modify_snapshot_settings (
                interval => 60,
                retention => 4320,
                topnsql => 100
              );
    end;
        
--五:删除AWR快照,再次查看SYSAUX表空间使用率,
    select min(snap_id),max(snap_id) from dba_hist_snapshot;//查询最最小和最大快照ID      
     select * from dba_hist_snapshot;
     
    begin
         dbms_workload_repository.drop_snapshot_range(
           low_snap_id => 37170,
          high_snap_id => 37370,
          dbid => 1296073523);
    end;
   

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29519108/viewspace-1982435/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-03-04

  • 博文量
    304
  • 访问量
    427314