sysaux表空间处理流程

embedded/2025/2/25 2:23: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.ppmy.cn/embedded/164932.html

相关文章

数仓搭建(hive):DWS层(服务数据层)

数仓搭建(hive):DWS层(服务数据层)

DWS层示例: 搭建日主题宽表 需求 维度 步骤 在hive中建数据库dws >>建表 CREATE DATABASE if NOT EXISTS DWS; 建表sql CREATE TABLE yp_dws.dws_sale_daycount( --维度 city_id string COMMENT 城市id, city_name string COMMENT 城市name, trade_area_id string COMME…
阅读更多...
C#导出dataGridView数据

C#导出dataGridView数据

方法一&#xff1a;导出为CSV文件&#xff08;简单快速&#xff09; 优点&#xff1a;无需依赖库&#xff0c;但格式简单。 private void ExportToCSV(DataGridView dataGridView, string filePath) {using (StreamWriter sw new StreamWriter(filePath, false, Encoding.UT…
阅读更多...
臻识相机，华夏相机，芊熠车牌识别相机加密解密

臻识相机，华夏相机，芊熠车牌识别相机加密解密

臻识&#xff0c;华夏&#xff0c;芊熠这三种车牌识别相机解密我都试过了&#xff0c;可以正常解密成功&#xff0c;其它品牌我暂时没有测试。超级简单&#xff0c;免费的&#xff0c;白嫖无敌&#xff01; 流程&#xff1a; ①&#xff1a;先导出配置文件&#xff0c;例如我以…
阅读更多...
安全面试4

安全面试4

文章目录 给的源码是ThinkPHP框架的话&#xff0c;审计起来和没有使用框架的有什么不同&#xff0c;从流程上或者从关注的点上有什么不同框架代码审计的流程无框架代码审计的流程 反序列的时候&#xff0c;unserialize()反序列一个字符串的时候&#xff0c;对象会有一些魔术方法…
阅读更多...
从卡顿到丝滑：火山引擎DeepSeek-R1引领AI工具新体验

从卡顿到丝滑：火山引擎DeepSeek-R1引领AI工具新体验

方舟大模型体验中心全新上线&#xff0c;免登录体验满血联网版Deep Seek R1 模型及豆包最新版模型:https://www.volcengine.com/experience/ark?utm_term202502dsinvite&acDSASUQY5&rcGO9H7M38 告别DeepSeek卡顿&#xff0c;探索火山引擎DeepSeek-R1的丝滑之旅 在A…
阅读更多...
网络运维学习笔记 016网工初级（HCIA-Datacom与CCNA-EI）PPP点对点协议和PPPoE以太网上的点对点协议（此处只讲华为）

网络运维学习笔记 016网工初级（HCIA-Datacom与CCNA-EI）PPP点对点协议和PPPoE以太网上的点对点协议（此处只讲华为）

文章目录 PPP&#xff08;Point to Point Protocol&#xff0c;点对点协议&#xff0c;华为默认使用&#xff09;实验1&#xff1a;设置为pap模式实验2&#xff1a;设置为chap模式&#xff08;实验1的基础上&#xff09; 串口封装协议HDLC&#xff08;High-Level Data Link Con…
阅读更多...
QT串口通信之二，实现单个温湿度传感器数据的采集(采用Qt-modbus实现)

QT串口通信之二，实现单个温湿度传感器数据的采集(采用Qt-modbus实现)

接上 QT串口通信之一，实现单个温湿度传感器数据的采集 上述文章中用QSerialPort实现了温湿度传感器的采集，实际上比较麻烦的，因为需要自定义解析帧， 接下来，用Qt-modbus-封装度更高的协议，来实现温湿度的采集； #include "MainWindow.h" #include "ui_M…
阅读更多...
RK Android11 WiFi模组 AIC8800 驱动移植调试记录

RK Android11 WiFi模组 AIC8800 驱动移植调试记录

RK Android11 WiFi模组 AIC8800 驱动移植调试记录 作者&#xff1a;Witheart更新时间&#xff1a;20250221 概要&#xff1a;本文记录了 RK3568 平台上移植并调试 WiFi6 模组 AIC8800 的过程&#xff0c;涵盖 WiFi 和蓝牙驱动的适配与问题排查。 配置流程在另一篇文章&#x…
阅读更多...
最新文章

Copyright @ 2022~2023