sysaux表空间处理流程

news/2025/2/22 23:40:49

1.查看节点1表空间情况

set line 200;

set pagesize 20000;

set feedback off;

col tablespace_name for a20;

col c_free_percent for a12;

col c_used_percent for a12;

col m_free_percent for a12;

col m_USED_PERCENT for a12;

select d.tablespace_name,round(d.MB_current_Bytes,2) Curr_Size_MB,round(f.f                                                                                                 ree_mb_bytes,2) Free_Szie_MB,round(d.MB_maxbytes,2) MAX_Size_MB,round((f.free_mb                                                                                                 _bytes/d.MB_current_Bytes)*100,2)  c_free_percent,round((d.MB_current_Bytes-f.fr                                                                                                 ee_mb_bytes)/d.MB_current_Bytes,4)*100 || '%' c_used_percent,round(((d.MB_maxbyt                                                                                                 es-d.MB_current_Bytes+f.free_mb_bytes)/d.MB_maxbytes)*100,2)   m_free_percent,ro                                                                                                 und((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_maxbytes,4)*100 || '%' m_used_perc                                                                                                 ent

  2  from  (select tablespace_name,sum(bytes/1024/1024) MB_current_Bytes,sum(max                                                                                                 bytes/1024/1024) MB_maxbytes from dba_data_files group by tablespace_name ) d,(s                                                                                                 elect tablespace_name,sum(bytes/1024/1024) free_mb_bytes from dba_free_space gro                                                                                                 up by tablespace_name) f

  3  where d.tablespace_name=f.tablespace_name

  4  order by c_free_percent ;

TABLESPACE_NAME      CURR_SIZE_MB FREE_SZIE_MB MAX_SIZE_MB C_FREE_PERCENT C_USED_PERCE M_FREE_PERCENT M_USED_PERCE

-------------------- ------------ ------------ ----------- -------------- ------------ -------------- ------------

SYSTEM                       5470        28.75    32767.98     ########## 99.47%           ########## 16.61%

SYSAUX                   32767.98       984.88    32767.98     ########## 96.99%           ########## 96.99%

UPRR_SP                     13300       680.56    32767.98     ########## 94.88%           ########## 38.51%

FITECH                   43007.98      4595.88    98303.95     ########## 89.31%           ########## 39.07%

UNDOTBS1                     3640      3587.25    32767.98     ########## 1.45%            ########## .16%

UNDOTBS2                     3975      3938.38    32767.98     ########## .92%             ########## .11%

USERS                     2856.25      2854.94    32767.98     ########## .05%             ########## 0%

SQL>

sysaux表空间使用率96.99%,需要进行清理否则会影响数据库正常运行。

2.查看V$SYSAUX_OCCUPANTS视图情况

SET LINES 120 pagesize 199;

COL OCCUPANT_NAME FORMAT A30;

SELECT * FROM (SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 GB FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC) WHERE ROWNUM<=5;

--AUDSYS 审计数据较多

SQL> SELECT * FROM (SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 GB FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC) WHERE ROWNUM<=5;

OCCUPANT_NAME                          GB

------------------------------ ----------

AUDSYS                         22.7640381

SM/ADVISOR                      6.1661377

SM/AWR                         1.06622314

SM/OPTSTAT                     .482543945

XDB                            .061401367

2.1清理审计数据

begin

dbms_audit_mgmt.clean_audit_trail(

audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

use_last_arch_timestamp  =>  FALSE);

end;

/

--清理完成

SQL> SELECT * FROM (SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 GB FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC) WHERE ROWNUM<=5;

OCCUPANT_NAME                          GB

------------------------------ ----------

SM/ADVISOR                      6.1661377

SM/AWR                         1.06622314

SM/OPTSTAT                     .482543945

XDB                            .061401367

SM/OTHER                       .051574707

SQL>

2.2SM/ADVISOR优化任务需要清理

--清理优化任务

SQL> DECLARE

v_tname VARCHAR2(32767);

BEGIN

v_tname := 'AUTO_STATS_ADVISOR_TASK';

DBMS_STATS.DROP_ADVISOR_TASK(v_tname);

END;

/

  alter table WRI$_ADV_OBJECTS move;

  alter index WRI$_ADV_OBJECTS_PK rebuild;

  alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;

  alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;

SQL> DECLARE

  2  v_tname VARCHAR2(32767);

  3  BEGIN

  4  v_tname := 'AUTO_STATS_ADVISOR_TASK';

  5  DBMS_STATS.DROP_ADVISOR_TASK(v_tname);

  6  END;

  7  /

SQL> alter table WRI$_ADV_OBJECTS move;

SQL> alter index WRI$_ADV_OBJECTS_PK rebuild;

SQL>   alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;

SQL>   alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;

SQL> SELECT * FROM (SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 GB FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC) WHERE ROWNUM<=5;

OCCUPANT_NAME                          GB

------------------------------ ----------

SM/AWR                         1.06634521

SM/OPTSTAT                     .482971191

SM/ADVISOR                     .103149414

XDB                            .061401367

SM/OTHER                       .051574707

SQL>

3.清理后sysaux表空间情况

SQL> set line 200;

SQL> set pagesize 20000;

SQL> set feedback off;

SQL> col tablespace_name for a20;

SQL> col c_free_percent for a12;

SQL> col c_used_percent for a12;

SQL> col m_free_percent for a12;

SQL> col m_USED_PERCENT for a12;

SQL> select d.tablespace_name,round(d.MB_current_Bytes,2) Curr_Size_MB,round(f.free_mb_bytes,2) Free_Szie_MB,round(d.MB_maxbytes,2) MAX_Size_MB,round((f.free_mb_bytes/d.MB_current_Bytes)*100,2)  c_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_current_Bytes,4)*100 || '%' c_used_percent,round(((d.MB_maxbytes-d.MB_current_Bytes+f.free_mb_bytes)/d.MB_maxbytes)*100,2)   m_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_maxbytes,4)*100 || '%' m_used_percent

  2  from  (select tablespace_name,sum(bytes/1024/1024) MB_current_Bytes,sum(maxbytes/1024/1024) MB_maxbytes from dba_data_files group by tablespace_name ) d,(select tablespace_name,sum(bytes/1024/1024) free_mb_bytes from dba_free_space group by tablespace_name) f

  3  where d.tablespace_name=f.tablespace_name

  4  order by c_free_percent ;

TABLESPACE_NAME      CURR_SIZE_MB FREE_SZIE_MB MAX_SIZE_MB C_FREE_PERCENT C_USED_PERCE M_FREE_PERCENT M_USED_PERCE

-------------------- ------------ ------------ ----------- -------------- ------------ -------------- ------------

SYSTEM                       5470        28.75    32767.98     ########## 99.47%           ########## 16.61%

UPRR_SP                     13300       679.56    32767.98     ########## 94.89%           ########## 38.51%

FITECH                   43007.98      4595.88    98303.95     ########## 89.31%           ########## 39.07%

UNDOTBS1                     3640       579.19    32767.98     ########## 84.09%           ########## 9.34%

SYSAUX                   32767.98     30502.25    32767.98     ########## 6.91%            ########## 6.91%

UNDOTBS2                     3975      3937.31    32767.98     ########## .95%             ########## .12%

USERS                     2856.25      2854.94    32767.98     ########## .05%             ########## 0%

清理后的sysaux表空间使用率已由之前的99.69%变成6.91%。

4.查看二节点sysaux表空间情况

set line 200;

set pagesize 20000;

set feedback off;

col tablespace_name for a20;

col c_free_percent for a12;

col c_used_percent for a12;

col m_free_percent for a12;

col m_USED_PERCENT for a12;

select d.tablespace_name,round(d.MB_current_Bytes,2) Curr_Size_MB,round(f.free_mb_bytes,2) Free_Szie_MB,round(d.MB_maxbytes,2) MAX_Size_MB,round((f.free_mb_bytes/d.MB_current_Bytes)*100,2)  c_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_current_Bytes,4)*100 || '%' c_used_percent,round(((d.MB_maxbytes-d.MB_current_Bytes+f.free_mb_bytes)/d.MB_maxbytes)*100,2)   m_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_maxbytes,4)*100 || '%' m_used_percent

from  (select tablespace_name,sum(bytes/1024/1024) MB_current_Bytes,sum(maxbytes/1024/1024) MB_maxbytes from dba_data_files group by tablespace_name ) d,(select tablespace_name,sum(bytes/1024/1024) free_mb_bytes from dba_free_space group by tablespace_name) f

where d.tablespace_name=f.tablespace_name

order by c_free_percent ;

 

TABLESPACE_NAME      CURR_SIZE_MB FREE_SZIE_MB MAX_SIZE_MB C_FREE_PERCENT C_USED_PERCE M_FREE_PERCENT M_USED_PERCE

-------------------- ------------ ------------ ----------- -------------- ------------ -------------- ------------

SYSTEM                       5470        28.75    32767.98     ########## 99.47%   ########## 16.61%

UPRR_SP                     13300       679.56    32767.98     ########## 94.89%   ########## 38.51%

FITECH                   43007.98      4595.88    98303.95     ########## 89.31%   ########## 39.07%

UNDOTBS1                     3640      1093.19    32767.98     ########## 69.97%   ########## 7.77%

SYSAUX                   32767.98     24294.44    32767.98     ########## 25.86%   ########## 25.86%

UNDOTBS2                     3975      3937.38    32767.98     ########## .95%    ########## .11%

USERS                     2856.25      2854.94    32767.98     ########## .05%    ########## 0%

SQL>

sysaux表空间使用率为25.86%未达到临界值无需处理。


http://www.niftyadmin.cn/n/5862847.html

相关文章

React 高阶组件的优缺点

React 高阶组件的优缺点

React 高阶组件的优缺点 优点 1. 代码复用性高 公共逻辑封装&#xff1a;当多个组件需要实现相同的功能或逻辑时&#xff0c;高阶组件可以将这些逻辑封装起来&#xff0c;避免代码重复。例如&#xff0c;多个组件都需要在挂载时进行数据获取操作&#xff0c;就可以创建一个数…
阅读更多...
前端如何转战鸿蒙

前端如何转战鸿蒙

前端如何转战鸿蒙系统 在当今技术日新月异的时代&#xff0c;前端开发者们不断探索新的领域和机会。随着鸿蒙系统的崛起&#xff0c;一个全新的生态正等待着前端开发者们去开拓。那么&#xff0c;作为前端开发者&#xff0c;我们为何要转战鸿蒙系统&#xff1f;又该如何顺利转型…
阅读更多...
前端面试之Box盒子布局：核心知识与实战解析

前端面试之Box盒子布局：核心知识与实战解析

目录 引言&#xff1a;布局能力决定前端高度 一、盒模型基础&#xff1a;看得见的像素战争 1. 标准盒模型 vs IE盒模型 2. 核心组成公式 3. 视觉格式化模型 二、传统布局三剑客 1. 浮动布局&#xff08;Float Layout&#xff09; 2. 定位布局&#xff08;Position Layou…
阅读更多...
Python实战：Excel中文转拼音工具开发教程

Python实战：Excel中文转拼音工具开发教程

在日常办公中&#xff0c;我们经常需要处理Excel文件&#xff0c;有时候需要将中文转换为拼音缩写以方便检索和使用。今天我将分享一个使用Python开发的小工具&#xff0c;它可以自动将Excel文件中指定列的中文转换为拼音缩写。 C:\pythoncode\new\ConvertExcelcontentToPinyin…
阅读更多...
图解MySQL【日志】——Redo Log

图解MySQL【日志】——Redo Log

Redo Log&#xff08;重做日志&#xff09; 为什么需要 Redo Log&#xff1f; 1. 崩溃恢复 数据库崩溃时&#xff0c;系统通过 Redo Log 来恢复尚未写入磁盘的数据。Redo Log 记录了所有已提交事务的操作&#xff0c;系统在重启后会重做这些操作&#xff0c;以保证数据不会丢…
阅读更多...
JUC并发—9.并发安全集合四

JUC并发—9.并发安全集合四

大纲 1.并发安全的数组列表CopyOnWriteArrayList 2.并发安全的链表队列ConcurrentLinkedQueue 3.并发编程中的阻塞队列概述 4.JUC的各种阻塞队列介绍 5.LinkedBlockingQueue的具体实现原理 6.基于两个队列实现的集群同步机制 4.JUC的各种阻塞队列介绍 (1)基于数组的阻塞…
阅读更多...
Springboot 高频面试题

Springboot 高频面试题

以下是Spring Boot的高频面试题及答案和底层原理解释&#xff1a; 基础概念 什么是Spring Boot&#xff0c;其主要特点是什么&#xff1f; 答案&#xff1a; Spring Boot本质上是一个建立在Spring框架之上的快速应用开发框架。其主要特点包括&#xff1a; 启动器&#xff1a;一…
阅读更多...
力扣LeetCode: 2209 用地毯覆盖后的最少白色砖块

力扣LeetCode: 2209 用地毯覆盖后的最少白色砖块

题目&#xff1a; 给你一个下标从 0 开始的 二进制 字符串 floor &#xff0c;它表示地板上砖块的颜色。 floor[i] 0 表示地板上第 i 块砖块的颜色是 黑色 。floor[i] 1 表示地板上第 i 块砖块的颜色是 白色 。 同时给你 numCarpets 和 carpetLen 。你有 numCarpets 条 黑…
阅读更多...
最新文章

Copyright @ 2022~2023