sysaux 表空间不足问题处理

Oracle从10G 开始引入的,以前一些使用独立表空间或系统表空间的数据库组件现在在SYSAUX表空间中创建. 通过分离这些组件和功能，S

一现场，备份库alter 日志出现错误
 ora-1688: unable to extend table sys.wrh$_sqlstat partition wrh$_sqlsta_3344221469_3956 by 128 in                tablespace sysaux
 ora-1688: unable to extend table sys.wrh$_sqlstat partition wrh$_sqlsta_3344221469_3956 by 128 in                tablespace sysaux
 
根据错误提示是sysaux 表空间满了
 
 oracle从10g 开始引入的,以前一些使用独立表空间或系统表空间的数据库组件现在在sysaux表空间中创建.
 通过分离这些组件和功能，system表空间的负荷得以减轻.反复创建一些相关对象及组件引起system表空间的碎片问题得以避免。
 如果sysaux表空间不可用,数据库核心功能将保持有效;使用sysaux表空间的特点将会失败或功能受限.
 
可以从v$sysaux_occupants view 查看情况
 
v$sysaux_occupants
 
v$sysaux_occupants displays sysaux tablespace occupant information.
 column  datatype  description
 occupant_name  varchar2(64)  occupant name
 occupant_desc  varchar2(64)  occupant description
 schema_name  varchar2(64)  schema name for the occupant
 move_procedure  varchar2(64)  name of the move procedure; null if not applicable
 move_procedure_desc  varchar2(64)  description of the move procedure
 space_usage_kbytes  number  current space usage of the occupant (in kb)
 
查看那些 occupant空间占用情况
 
select occupant_name,space_usage_kbytes/1024/1024 from v$sysaux_occupants;
 select s.object_name from dba_objects s where s.object_name like '%optstat%' and s.object_type='table';
 

在Oracle10中表空间SYSAUX引入，oracle把统计信息存储在这里，这也是为了更好的优化system表空间，
 我们可以用视图V$SYSAUX_OCCUPANTS 查看，oracle有哪些数据存贮在SYSAUX中。
 

 
 oracle的SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER的统计信息都存储在SYSAUX中
 
 查询当前SM/OPTSTAT的统计信息的保存时间
 SQL> select dbms_stats.get_stats_history_retention from dual;
 
 GET_STATS_HISTORY_RETENTION
 ---------------------------
                          31
 
 修改SM/OPTSTAT的统计信息的保存时间为10天
 SQL> exec dbms_stats.alter_stats_history_retention(10);
 
 PL/SQL procedure successfully completed
 
 SQL> select dbms_stats.get_stats_history_retention from dual;
 
 GET_STATS_HISTORY_RETENTION
 ---------------------------
                          10
 
 

删除16天前的统计数据
 SQL> exec dbms_stats.purge_stats(sysdate-20);
 
 PL/SQL procedure successfully completed
 
 SQL>

查看当前有效的统计数据是到什么时间的
 SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
 
 GET_STATS_HISTORY_AVAILABILITY
 --------------------------------------------------------------------------------
 12-2月 -12 07.15.49.000000000 下午 +08:00
 
 再删除7天前的统计数据
 SQL> exec dbms_stats.purge_stats(sysdate-7);
 
 PL/SQL procedure successfully completed
 
 这个时候发现有效的统计信息时间已经变了
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
 
 GET_STATS_HISTORY_AVAILABILITY
 --------------------------------------------------------------------------------
 14-2月 -12 07.15.57.000000000 下午 +08:00