Re: Resize SYSAUX datafile
- From: Ricard Martínez <ricard.martinez@xxxxxxxxx>
- To: Dennis Williams <oracledba.williams@xxxxxxxxx>
- Date: Thu, 7 Apr 2011 11:57:28 +0200
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@xxxxxxxxx> 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@xxxxxxxxxx> 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
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
Other related posts:
