Oracle Database Discussions

All

In our production environment we had been running out of SYSAUX tablespace for sometime and we have added about 15 datafiles-2G each

select dbms_stats.get_stats_history_retention from dual;
31

i changed this to 15

Now i am trying to reorg the component holding the old data by reorging the associated tables and indices

alter table WRI$_OPTSTAT_SYNOPSIS_PARTGRP move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_HEAD$ move tablespace SYSAUX;
alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_IND_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_OPR move tablespace SYSAUX;
alter table WRH$_OPTIMIZER_ENV move tablespace SYSAUX;
alter table WRH$_PLAN_OPTION_NAME move tablespace SYSAUX;

as well as the indices

alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_SYNOPPARTGRP rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_SYNOPHEAD rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_TAB_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_IND_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_HH_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_H_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_AUX_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_STIME rebuild online parallel (degree 14);
alter index WRH$_OPTIMIZER_ENV_PK rebuild online parallel (degree 14);
alter index WRH$_PLAN_OPTION_NAME_PK rebuild online parallel (degree 14);


the problem is that the below command fails for one of the tables


SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace SYSAUX
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8192 in tablespace SYSAUX


which means i need to add another datafile where as my purpose is to get rid of space in the existing datafile

Is there a better way to achieve what i am trying to do.
Also is there a way to recycle the datafiles associated with SYSAUX by creating another SYSAUX tablespace and dropping contents of older one etc

I am using the following doc

http://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/