sysaux表空间非常大，怎么处理

wxlun

db：11.2.0.3
os：redhat 5.6 x64

有一个数据库sysaux表空间已经非常大，使用10G左右，怎么处理一下，空间能否回收一下，谢谢，sysaux比较大的对象如下：


SQL> select owner,segment_name,bytes/1024/1024 from dba_segments seg where seg.tablespace_name='SYSAUX' order by seg.bytes desc;

OWNER                          SEGMENT_NAME                                                                     BYTES/1024/1024
------------------------------ -------------------------------------------------------------------------------- ---------------
SYS                            WRH$_EVENT_HISTOGRAM_PK                                                                     1478
SYS                            WRH$_EVENT_HISTOGRAM                                                                        1415
SYS                            AUD$                                                                                        1287
SYS                            SYS_LOB0000006283C00004$$                                                                   1152
SYS                            WRH$_RSRC_CONSUMER_GROUP                                                                     985
SYS                            WRH$_ACTIVE_SESSION_HISTORY                                                                  450
SYS                            WRH$_RSRC_CONSUMER_GROUP_PK                                                                  447
SYS                            WRH$_SQL_BIND_METADATA                                                                       399
SYS                            WRH$_SQL_BIND_METADATA_PK                                                                    368
SYS                            WRM$_SNAPSHOT_DETAILS_INDEX                                                                  235
SYS                            WRM$_SNAPSHOT_DETAILS                                                                        224
SYS                            WRH$_SYSSTAT_PK                                                                              160
SYS                            WRH$_SYSSTAT                                                                                 156
SYS                            WRH$_PARAMETER                                                                               133
SYS                            WRH$_MVPARAMETER                                                                             115
SYS                            WRH$_SEG_STAT                                                                                 93
SYS                            WRH$_PARAMETER_PK                                                                             91
SYS                            WRH$_MVPARAMETER_PK                                                                           80
SYS                            WRH$_SERVICE_STAT_PK                                                                          72
SYS                            WRH$_ACTIVE_SESSION_HISTORY_PK                                                                54

robin

aud$ 是可以移动的。 WRH 依赖于你辅助信息的收集时间。 默认收集7天数据 。先解决这个问题 才能保证辅助表空间的大小不会太大。

xifenfei

1. 审计如果不需要，考虑关闭
2. 才10g，一点不大

Maclean Liu(刘相兵

相关帖子

http://t.askmaclean.com/thread-11-1-1.html

Maclean Liu(刘相兵

1、 AWR快照保留时间多长？
2、 10g并不算非常大

3、 WRH$_EVENT_HISTOGRAM  非系统关键表 可以truncate掉

ALLSTARS_ORACLE

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged (Doc ID 387914.1)

Symptoms

AWR tables are not being purged according to settings in sys.wrm$_wr_control. Because of this the tables are accumulating more and more rows and the segments associated with these tables become very large.
Cause

Oracle is deciding what rows need to be purged based on the retention policy. There is a special mechanism which is used in the case of the large AWR tables. For the large AWR tables, we store the snapshot data into partitions. Purging data from these tables means that a partition gets dropped. During the nightly purge task, we only drop the partition if all the data in the partition has expired. If the partition contains at least one row which, according to the retention policy shouldn't be removed,  then  the partition won't be dropped and as such the table will contain old data.

Based on the above clarifications we can say that if anything is going wrong with the partition split operation then we will end up by having the AWR tables not to be purged.

The root cause for the problems can be diagnosed by triggering the same operations which are executed each time the purge job is fired.
Solution

1) Check how many partitions do exist for the offending table :      
   
   
select table_name,partition_name
from dba_tab_partitions
where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';



2) Try to force the execution of the purge operations :




alter session set "_swrf_test_action" = 72;

This step can finish successfully :

Statement processed

or it can fail:

ORA-02097: parameter cannot be modified because specified value is invalid
ORA-13509: error encountered during updates to a AWR table
ORA-14327: Some index [sub]partitions could not be rebuilt



3) If the operation on step 2 is failing the generated (udump) trace file must be analyzed. In the trace file a partition split statement can be found :



*** KEWROCISTMTEXEC - encountered error: (ORA-14327: Some index [sub]partitions could not be rebuilt
ORA-20001: Component "WRH$_SQLSTA_2446360981_11691" does not exist on index "WRH$_SQLSTAT_INDEX" in schema "SYS".
ORA-06512: at "SYS.DBMS_I_INDEX_UTL", line 748
ORA-06512: at "SYS.DBMS_INDEX_UTL", line 397
ORA-06512: at "SYS.DBMS_INDEX_UTL", line 556
ORA-06512: at line 1
)
*** SQLSTR: total-len=228, dump-len=228,
STR={alter table WRH$_SQLSTAT split partition
WRH$_SQLSTA_2446360981_11691 at (2446360981,12774) into (partition
WRH$_SQLSTA_2446
360981_11691 tablespace SYSAUX, partition WRH$_SQLSTA_2446360981_12774
tablespace SYSAUX) update indexes}






The split statement must be executed manually :      


   

alter table WRH$_SQLSTAT split partition WRH$_SQLSTA_2446360981_11691 at (2446360981,12774)
into (partition WRH$_SQLSTA_2446 360981_11691 tablespace SYSAUX, partition WRH$_SQLSTA_2446360981_12774 tablespace SYSAUX)
update indexes;

4) Steps 2 and 3 must be executed until setting the event is successful.

5) Check how many partitions do exist for the offending table after all the above actions :
set serveroutput on
declare
CURSOR cur_part IS
SELECT partition_name from dba_tab_partitions
WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

query1 varchar2(200);
query2 varchar2(200);

TYPE partrec IS RECORD (snapid number, dbid number);
TYPE partlist IS TABLE OF partrec;

Outlist partlist;
begin
dbms_output.put_line('PARTITION NAME SNAP_ID DBID');
dbms_output.put_line('--------------------------- ------- ----------');

for part in cur_part loop
query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
execute immediate query1 bulk collect into OutList;

if OutList.count > 0 then
for i in OutList.first..OutList.last loop
dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);
end loop;
end if;

query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
execute immediate query2 bulk collect into OutList;

if OutList.count > 0 then
for i in OutList.first..OutList.last loop
dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);
dbms_output.put_line('---');
end loop;
end if;

end loop;
end;
/

We should have more partitions now and it is expected that the old partitions will get automatically removed when the retention period will expire.


Once you have identified, and split the partitions, you may want to drop a partition range, by dropping a snapshot range:
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);