SYSAUX表空间使用率过高的问题处理
- Oracle数据库的表空间主要分为两类,一类是数据库系统表空间,例如SYSTEM, SYSAUX, TEMP, UNDO,另一类是用户自定义表空间,主要适用于各种业务的数据处理。SYSTEM表空间主要用来存储数据库的数据字典和元数据信息,其表空间的容量大小随着数据库对象的规模而变化;而SYSAUX表空间诞生于10G,作为SYSTEM表空间的辅助空间,被设计用来存储数据库对象的统计信息以及数据库快照等历史性能数据,其空间容量随着时间的推移不断地膨胀,我们需要根据oracle的相应的管理方式来设置管理策略,设定历史数据的保留时间等。
- 而UNDO 和TEMP表空间的容量则与数据库的活跃程度相关,数据库越是活跃,事务处理规模越大则相应空间容量的占用会增加。我们需要根据业务所产生的数据库事务的规模设置TEMP和UNDO表空间的容量。
- 我们使用以下的SQL语句查询数据库系统表空间的使用率信息:
SELECT * FROM ( SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS "SUM_BLOCKS", SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)", FREE_SPACE || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS SUM_BLOCKS, USED_SPACE || 'M' "USED_SPACE(M)", ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1) WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP');
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M) --------------- ----------- --------- ------------ ----------- ------------ SYSAUX 840M 107520 792.31M 94.32% 47.69M SYSTEM 810M 103680 809.56M 99.95% .44M TEMP 47M 6016 47M 100% 0M UNDOTBS1 405M 51840 404M 99.75% 1M
- 我们使用以下语句查询SYSAUX表空间内各个分类项目占存储空间的比重:
SELECT occupant_name "Item", space_usage_kbytes / 1048576 "Space Used (GB)", schema_name "Schema", move_procedure "Move Procedure" FROM v$sysaux_occupants ORDER BY 1
-
修改统计信息的保持时间,默认为31天,这里修改为7天,过期的统计信息会自动被删除:
(1)查询数据库统计信息的保留时间
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION --------------------------- 31
(2)更改数据库统计信息的保留时间为7天
SQL> exec dbms_stats.alter_stats_history_retention(7); PL/SQL procedure successfully completed.
(3)再次查询数据库统计信息的保留时间
SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION --------------------------- 7
-
修改AWR快照的保存时间为7天(7*24*60),每小时收集一次,也可以通过EM界面查看和修改
(1)查询数据库快照的MIN(SNAP_ID)和MAX(SNAP_ID)
SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID) ------------ ------------ 701 716
(2)如果修改数据库快照的保留时间出现错误ORA-13541与ORA-06512,处理方法如下所示
ERROR
begin
*
第 1 行出现错误:
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 39
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 87
ORA-06512: 在 line 2
*
第 1 行出现错误:
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 39
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 87
ORA-06512: 在 line 2
-
查询ORA-13541的错误信息如下:
ORA-13541: system moving window baseline size (string) greater than retention (string)
Cause: The system moving window baseline size must be less than the retention setting.
The specified window size or retention violate this.
Action:Check the moving window baseline size or retention.
Cause: The system moving window baseline size must be less than the retention setting.
The specified window size or retention violate this.
Action:Check the moving window baseline size or retention.
-
在看看上面的个数字基线大小(691200)和保留时间(604800)
-
基线大小691200
SQL> select 691200/60/60/24 from dual; 691200/60/60/24 --------------- 8 Note:8天的时间
-
保留时间604800
SQL> select 604800/60/24/60 from dual; 604800/60/24/60 --------------- 7
-
检查当前的移动窗口基线大小
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline; DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE ---------- ------------------------ ------------- ------------------ 4096851118 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
-
与上面的8天对应的数字(8*60*60*24=691200)相吻合.
-
调用如下过程修改移动窗口baseline size为7天
SQL> exec dbms_workload_repository.modify_baseline_window_size(7); PL/SQL 过程已成功完成。
-
然后继续上面的修改操作
SQL> begin dbms_workload_repository.modify_snapshot_settings ( interval => 30, retention => 7*24*60, topnsql => 100 ); end; / PL/SQL 过程已成功完成。
SQL> select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- --------------------- --------------------- ---------- 4096851118 +00000 00:30:00.0 +00007 00:00:00.0 100
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline; DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE ---------- ----------------------- ------------- ------------------ 4096851118 SYSTEM_MOVING_WINDOW MOVING_WINDOW 7
- 在Enterprise Manager中修改快照的保留时间
相关推荐
实际工作中难免会遇到表/用户等指向表空间不明确,或者是默认指向了系统表空间(system/sysaux)这种情况。存储空间往往都是有限的,所以当碰到这类问题的时候该怎么去清理释放系统表空间是必备知识。
本文在个人的实验环境中,测试解决了下Oracle 11.2.0.4 RAC sysaux表空间异常增长的处理过程。在Oracle 11.2.0.4 RAC下这是一个BUG,BUG号:14084247。
DB到12.2.0.1版本之后,由于统计信息顾问导致SYSAUX 过快增长
SYSAUX表空间是在10g之后引入的一个新的表空间,主要用于减轻对SYSTEM表空间的压力而作为SYSTEM表空间的辅助表空间。 原来存放于SYSTEM表空间的很多组件以及一些数据库元数据在10g中被移植到SYSAUX表空间。
表空间:是一个或多个数据文件的逻辑集合 表空间逻辑存储对象:永久段-->...表空间分类:系统表空间(system、sysaux),非系统表空间 一个表空间至少包含一个数据文件,一个数据文件只能属于一个表空间。
需求: 需要整理现场用户创建的表空间以及其存储数据,进行规范化管理。...以前一些使用独立表空间或系统表空间的数据库组件现在在SYSAUX表空间中创建.SYSAUX 表空间存放一些其他的 metadata 组件,如 OEM,Streams 等会
oracle_管理表空间 一个数据库有: SYSTEM、SYSAUX、TEMP三个默认表空间 一个或多个临时表空间 一个撤销表空间 几个应用程序专用的应用表空间
oracle中表空间、权限、角色、用户的总结,其中包括表空间的建立,权限的配置,角色的生成和用户的挂接
表空间分类 1.永久性表空间,如system,users。一般保存表,视图,过程和索引等数据 2.临时性表空间,如temp。只用于保存系统中短期活动的数据 3.撤销表空间,如undo。用来帮助回退未提交的事务数据 创建表空间 通过...
sysaux
本文讲述了Oracle修改表空间大小的方法。分享给大家供大家参考,具体如下: 1)查看各表空间分配情况 SQL> select tablespace_name, sum(bytes) / 1024 / 1024 from dba_data_files group by tablespace_name; ...
转载 SYSAUX表空间-SYS Auxiliary Oracle10g跨平台传输表空间的使用 Oracle 10g新特性expdp与传统exp的速度比较 Oracle10g新特性expdp/impdp的停止与重启动
Which is true about the SYSTEM and SYSAUX...-lhrbest-Which is true about the SYSTEM and SYSAUX...-lhrbest-
1 简介 课程目标 1 2 建议日程表 1 3 课程目标 1 4 Oracle 产品和服务 1 5 Oracle Database 10g :“g ”代表网格 1 6 ... 表空间和数据文件 1 17 SYSTEM 和SYSAUX 表空间 1 18 ...
83.未建索引的表(不包含表空间为'SYSTEM', 'SYSAUX', 'SYSMAN', 'USERS', 'TEMP'下的用户) 84.sort_segment检查 85.数据库总大小(Gb) 86.超过2g的segment(单个表超过2g建议使用分区表) 87.数据库长事务(执行...
SYSTEM 和SYSAUX 表空间1-18 段、区和块1-19 逻辑和物理数据库结构1-20 课程示例:HR 方案1-22 数据库体系结构:结构化组件概要1-23 小结1-24 2 安装Oracle 数据库软件 课程目标2-2 Oracle 数据库管理员的任务2-3 ...
SYSTEM 和 SYSAUX 表空间 1-18 段、区和块 1-19 逻辑和物理数据库结构 1-20 课程示例: HR 方案 1-22 数据库体系结构:结构化组件概要 1-23 小结 1-24 2 安装 Oracle 数据库软件 课程目标 2-2 Oracle ...
管理表空间 -system 存放数据字典信息,必须的,创建数据库时第一个创建 -sysaux 10g新,必须的,辅助分担system的负荷,系统管理如oem等三方工具等 -undo 存储回滚段信息,提供事务回滚功能 -temp 存放...
1.2.4 Sysaux表空间 1.2.5 默认的临时表空间 1.2.6 Undo表空间 1.2.7 服务器参数文件 1.2.8 后台进程 1.2.9 数据库管理员 1.3 Oracle Database 11g的基本数据类型 1.3.1 varchar2 1.3.2 数字 1.3.3 日期 1.3.4 ...