RE: AW: Resize SYSAUX datafile

I learned the hard way and it's quite obvious once you think about it. Before you attempt to reorg the sysaux tablespace and analyze the position of the segments to move, you need to purge the recyclebin. In my case, I could not see what was actually occupying the tablespace until I did.  

> From: Petr.Novak_at_trivadis.com
> To: ricard.martinez_at_gmail.com; oracledba.williams_at_gmail.com
> CC: grzegorzof_at_interia.pl; oracle-l_at_freelists.org
> Subject: AW: Resize SYSAUX datafile
> Date: Thu, 7 Apr 2011 10:27:50 +0000
>
> Hallo Ricard,
>
> may be you should get overview of extents in the tablespace:
>
> column MBytes format 9G999D99
>
> select /*+ RULE */ * from (select substr(segment_name,1,32) Segment,file_id,block_id,blocks,bytes/1024/1024 MBytes
> from dba_extents
> where tablespace_name ='SYSAUX'
> union
> select '---------',file_id,block_id,blocks,bytes/1024/1024 MBytes
> from dba_free_space
> where tablespace_name ='SYSAUX') order by file_id, block_id;
>
> Then you should identify only appropriate candidates for move/rebuild and try to move them in other tablespace, shrink the SYSAUX and move back.
>
>
> Best Regards,
> Petr
>
>
> Von: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org]" im Auftrag von "Ricard Martínez [ricard.martinez_at_gmail.com]
> Gesendet: Donnerstag, 7. April 2011 11:57
> Bis: Dennis Williams
> Cc: grzegorzof_at_interia.pl; oracle-l_at_freelists.org
> Betreff: Re: Resize SYSAUX datafile
>
> Hi
> I will explain better myself.
> Oracle 10.2.0.4, i want to reduce SYSAUX datafile, cause after purging statistics it have 5G, but only 800M used (ye i know disk is cheap, but isnt my call)
>
> I have use Reduce SYSAUX tablespace occupancy due to fragmented TABLE's and INDEX'es [ID 1271178.1]
>
> When rebuilding tables to tbs sysaux, i hit some:
>
> ORA-25191: cannot reference overflow table of an index-organized table (for example SYS.SYS_IOT_OVER_9830)
> and
> ORA-00997: illegal use of LONG datatype (for example SYSMAN.MGMT_PORTLET_PREFERENCE_STORE)
>
> I understand both errors are normal for some tables, so only can do is ignore them.
>
> Then i procced to rebuild indexes, and hit some:
>
> ORA-28650: Primary index on an IOT cannot be rebuilt (for example SYSMAN.MGMT_DB_INIT_PARAMS_ECM_PK)
>
> Normal too.
>
> Then after doing this if i check how much space can resize i see that only can resize 20m down, cause HWM is very high.
>
> Then i tried to do Shrink space cascade on tables and hit:
>
> ORA-10631: SHRINK clause should not be specified for this object (for example SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY)
> ORA-10662: Segment has long columns (for example EXFSYS.EXF$PLAN_TABLE)
>
> So not able to resize because HWM still too high.
>
> The only i can check still is to move all occupants of SYSAUX that can be moved (SELECT occupant_name, schema_name, move_procedure, move_procedure_desc FROM v$sysaux_occupants;), but there are some that cant, and i wonder if this would help me or not.
>
> So my question is:
> Have been anyone able to resize down the SYSAUX tablespaces with any method, trick, metalink note??
> Thanks
>
>
>
>
>
>
>
>
>
>
>
>
> On Wed, Apr 6, 2011 at 8:40 PM, Dennis Williams <oracledba.williams_at_gmail.com<mailto:oracledba.williams_at_gmail.com>> wrote:
> Richard,
>
> Do you want to increase the size of the SYSAUX datafile or reduce it?
>
> Dennis Williams
>
> On Wed, Apr 6, 2011 at 12:49 PM, Grzegorz Goryszewski <grzegorzof_at_interia.pl<mailto:grzegorzof_at_interia.pl>> wrote:
> On 2011-04-06 19:25, Ricard Martínez wrote:
> > Hi
> > Im trying to do a resize of SYSAUX datafile, but all the info and metalink
> > notes im reading, make me think it is impossible for the moment. That the
> > only real options is
> > to clean space and put tablespace in autoextend off.
> > Anyone have been able to do it? Or really isnt allowed atm?
> > Thanks
> >
> Hi,
> that's strange . There is nothing special about SYSAUX , You can check
> what made it big via
> |select * from V$SYSAUX_OCCUPANTS and do some cleanups.
> Can u share ORA- You are hitting ?
> Regards.
> GregG
>
> |
>
> ----------------------------------------------------------------
> Najwiekszy wybor samochodow nowych i uzywanych!
> Sprawdz >> http://linkint.pl/f2970
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
                                               

--
http://www.freelists.org/webpage/oracle-l