Re: Resize SYSAUX datafile

I alredy checked HWM segments before asking but since these are Oracle internal users/objects (DBSNMP, XDB and CTXSYS, AWR, etc) such techniques may cause corruption or render the database unusable.

And if you check v$sysaux_occupants you will see that are some occupant that didnt have proc to move them.

Thanks anyway.
Regards.

On Thu, Apr 7, 2011 at 12:27 PM, Petr Novak <Petr.Novak_at_trivadis.com> wrote:

> 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]&quot;
> im Auftrag von &quot;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