sysaux表空间爆满

喝醉酒的小白

已于 2024-06-08 11:00:07 修改

阅读量2k

点赞数 1

分类专栏： DBA 文章标签： dba 数据库

于 2022-07-09 14:55:35 首次发布

本文链接：https://blog.csdn.net/hezuijiudexiaobai/article/details/125677219

版权

DBA 专栏收录该内容

723 篇文章 17 订阅

订阅专栏

结论：11g查看发现，AWR占用空间较多，保留时间为默认，于是添加数据文件。

@tbs

SELECT F.TABLESPACE_NAME TABLESPACE_NAME,
            ROUND(((D.SUMBYTES + D.EXTEND_BYTES) / 1024 / 1024 / 1024), 2) TOTAL_G,
            ROUND((F.SUMBYTES + D.EXTEND_BYTES) / 1024 / 1024 / 1024, 2) FREE_G,
            ROUND((D.SUMBYTES - F.SUMBYTES) / 1024 / 1024 / 1024, 2) USED_G,
            ROUND((D.SUMBYTES - F.SUMBYTES) * 100 /
                  (D.SUMBYTES + D.EXTEND_BYTES),
                  2) USED_PERCENT
       FROM (SELECT TABLESPACE_NAME, SUM(BYTES) SUMBYTES
               FROM DBA_FREE_SPACE
              GROUP BY TABLESPACE_NAME) F,
            (SELECT TABLESPACE_NAME,
                    SUM(AA.BYTES) SUMBYTES,
                    SUM(AA.EXTEND_BYTES) EXTEND_BYTES
               FROM (SELECT NVL(CASE
                                  WHEN AUTOEXTENSIBLE = 'YES' THEN
                                   (CASE
                                     WHEN (MAXBYTES - BYTES) >= 0 THEN
                                      (MAXBYTES - BYTES)
                                   END)
                                END,
                                0) EXTEND_BYTES,
                            TABLESPACE_NAME,
                            BYTES
                       FROM DBA_DATA_FILES) AA
              GROUP BY TABLESPACE_NAME) D
      WHERE F.TABLESPACE_NAME = D.TABLESPACE_NAME
      ORDER BY USED_PERCENT DESC;

在这里插入图片描述

select * from (select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 GB  
	from dba_segments 
	where tablespace_name='SYSAUX'
	group by owner,segment_name,segment_type order by 4 desc ) 
where rownum <10;

在这里插入图片描述

select occupant_name, space_usage_kbytes/1024/1024 from v$sysaux_occupants order by 2;

在这里插入图片描述

SYSAUX表空间占用过大

SYSAUX表空间爆满

select owner,segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_EVENT_HISTOGRAM';

在这里插入图片描述

set long 999      
select dbms_metadata.get_ddl('TABLE','WRH$_EVENT_HISTOGRAM','SYS') from dual;

在这里插入图片描述

select min(snap_id),max(snap_id) from  wrh$_active_session_history;

exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id =>430,high_snap_id =>440);

select min(snap_id),max(snap_id) from  wrh$_active_session_history;