19c sysaux表空间爆满

来源：这里教程网时间：2026-03-03 21:13:55 作者：

## 第一步：检查表空间情况巡检发现 sysaux 表空间不足 TABLESPACE_NAME TP_REAL_SIZE TP_REAL_FREE_SI TP_FREE_RATING ############################## ############### ############### ############## SYSAUX 31.99GB 3.94GB 12.31% SYSTEM 31.99GB 25.15GB 78.61% TSP_TPACS 54.95GB 5.83GB 10.61% UNDOTBS1 31.99GB 21.98GB 68.70% USERS 31.99GB 31.99GB 99.99% 检查发现是 WRI$_ADV_OBJECTS 对象占用过多。 ## 第二步：查看参数配置此问题可以通过如下文档分析解决： How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1) To BottomTo Bottom 默认保留30天数据，查询如下 col parameter_name format a35 col parameter_value format a20 set lines 120 select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE'; SQL> SQL> SQL> SQL> TASK_NAME PARAMETER_NAME PARAMETER_VALUE ######################### ################################### #################### AUTO_STATS_ADVISOR_TASK EXECUTION_DAYS_TO_EXPIRE 30 ## 第三步：查看作业和数据情况数据信息如下： SQL> col task_name format a25 col EXECUTION_NAME format a15 select TASK_ID,TASK_NAME,EXECUTION_NAME ,to_date(execution_start,'yyyy/mm/dd hh24:mi:ss') from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK'; SQL> SQL> TASK_ID TASK_NAME EXECUTION_NAME TO_DATE(E ########## ######################### ############### ######### 6 AUTO_STATS_ADVISOR_TASK EXEC_3921 24#DEC#15 6 AUTO_STATS_ADVISOR_TASK EXEC_4351 24#DEC#16 6 AUTO_STATS_ADVISOR_TASK EXEC_4611 24#DEC#17 6 AUTO_STATS_ADVISOR_TASK EXEC_4881 24#DEC#18 6 AUTO_STATS_ADVISOR_TASK EXEC_5141 24#DEC#19 6 AUTO_STATS_ADVISOR_TASK EXEC_5301 24#DEC#20 6 AUTO_STATS_ADVISOR_TASK EXEC_5551 24#DEC#21 6 AUTO_STATS_ADVISOR_TASK EXEC_5621 24#DEC#22 6 AUTO_STATS_ADVISOR_TASK EXEC_5891 24#DEC#23 6 AUTO_STATS_ADVISOR_TASK EXEC_6121 24#DEC#24 6 AUTO_STATS_ADVISOR_TASK EXEC_6351 24#DEC#25 TASK_ID TASK_NAME EXECUTION_NAME TO_DATE(E ########## ######################### ############### ######### 6 AUTO_STATS_ADVISOR_TASK EXEC_6601 24#DEC#26 6 AUTO_STATS_ADVISOR_TASK EXEC_6851 24#DEC#27 6 AUTO_STATS_ADVISOR_TASK EXEC_7021 24#DEC#28 6 AUTO_STATS_ADVISOR_TASK EXEC_7041 24#DEC#29 6 AUTO_STATS_ADVISOR_TASK EXEC_7171 24#DEC#30 6 AUTO_STATS_ADVISOR_TASK EXEC_7341 24#DEC#31 6 AUTO_STATS_ADVISOR_TASK EXEC_7511 25#JAN#01 6 AUTO_STATS_ADVISOR_TASK EXEC_7671 25#JAN#02 6 AUTO_STATS_ADVISOR_TASK EXEC_7761 25#JAN#03 6 AUTO_STATS_ADVISOR_TASK EXEC_7901 25#JAN#04 6 AUTO_STATS_ADVISOR_TASK EXEC_7931 25#JAN#05 TASK_ID TASK_NAME EXECUTION_NAME TO_DATE(E ########## ######################### ############### ######### 6 AUTO_STATS_ADVISOR_TASK EXEC_8091 25#JAN#06 6 AUTO_STATS_ADVISOR_TASK EXEC_8251 25#JAN#07 6 AUTO_STATS_ADVISOR_TASK EXEC_8361 25#JAN#08 6 AUTO_STATS_ADVISOR_TASK EXEC_8512 25#JAN#09 6 AUTO_STATS_ADVISOR_TASK EXEC_8592 25#JAN#10 6 AUTO_STATS_ADVISOR_TASK EXEC_8682 25#JAN#11 6 AUTO_STATS_ADVISOR_TASK EXEC_8723 25#JAN#12 6 AUTO_STATS_ADVISOR_TASK EXEC_8912 25#JAN#13 ## 第四步：进行修改和清理我们修改为只保留3天数据 EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (task_name => 'AUTO_STATS_ADVISOR_TASK', parameter => 'EXECUTION_DAYS_TO_EXPIRE', value => 3); 运行作业清理数据： SQL> conn / as sysdba SQL> exec prvt_advisor.delete_expired_tasks; 清理高水位： 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> col task_name format a25 col EXECUTION_NAME format a15 select TASK_ID,TASK_NAME,EXECUTION_NAME ,to_date(execution_start,'yyyy/mm/dd hh24:mi:ss') from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';SQL> SQL> TASK_ID TASK_NAME EXECUTION_NAME TO_DATE(E ########## ######################### ############### ######### 6 AUTO_STATS_ADVISOR_TASK EXEC_18745 25#JAN#11 6 AUTO_STATS_ADVISOR_TASK EXEC_18756 25#JAN#12 6 AUTO_STATS_ADVISOR_TASK EXEC_18804 25#JAN#13 SQL> col parameter_name format a35 col parameter_value format a20 set lines 120 select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE'; SQL> SQL> SQL> TASK_NAME PARAMETER_NAME PARAMETER_VALUE ######################### ################################### #################### AUTO_STATS_ADVISOR_TASK EXECUTION_DAYS_TO_EXPIRE 3 表空间信息： SQL> select /*+rule */ all_tp.TP_NAME as "TABLESPACE_NAME", 2 to_char(trunc(all_tp.TP_ALL_SIZE_KB / 1024 / 1024, 2), 'FM9999990.0099') || 'GB' as "TP_REAL_SIZE", to_char(trunc(free_tp.TP_FREE_SIZE_KB / 1024 / 1024, 2), 'FM9999990.0099') || 'GB' as "TP_REAL_FREE_SIZE 3 ", to_char(trunc(free_tp.TP_FREE_SIZE_KB * 100 / all_tp.TP_ALL_SIZE_KB, 2), 'FM9999990.0099') || '%' as "TP_FREE_RATING" from ( select 4 /*+rule */ sum(decode(sign(ddf.maxbytes # ddf.bytes), 1, ddf.maxbytes, ddf.bytes)) / 1024 as "TP_ALL_S 5 IZE_KB", ddf.tablespace_name as "TP_NAME" from DBA_DATA_FILES ddf group by ddf.tablespace_name) all_tp, 6 ( select /*+rule */ sum(ifree_tp.TPF_SIZE_KB) as "TP_FREE_SIZE_KB", ifree_tp.TP_NAME from ( select /*+rule */ dfs.bytes / 1024 as "TPF_SIZE_KB", 7 8 9 10 11 12 13 14 15 16 dfs.tablespace_name as "TP_NAME" 17 from DBA_FREE_SPACE dfs union all select /*+rule */ decode(sign(ddf.maxbytes # ddf.bytes), 1, 18 ddf.maxbytes # ddf.bytes, 0) / 1024 as "TPF_SIZE_KB", 19 20 21 22 23 24 25 26 27 28 ddf.tablespace_name as "TP_NAME" from DBA_DATA_FILES ddf where ddf.autoextensible = 'YES') ifree_tp group by ifree_tp.TP_NAME) free_tp where all_tp.TP_NAME = free_t 29 p.TP_NAME(+) ; 30 31 32 33 TABLESPACE_NAME TP_REAL_SIZE TP_REAL_FREE_SI TP_FREE_RATING ############################## ############### ############### ############## SYSAUX 31.99GB 23.86GB 74.57% SYSTEM 31.99GB 25.15GB 78.61% TSP_TPACS 54.95GB 5.83GB 10.61% UNDOTBS1 31.99GB 21.98GB 68.70% USERS 31.99GB 31.99GB 99.99% 参考文档： How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

编辑推荐：

返回列表

19c sysaux表空间爆满

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推