AW: Resize SYSAUX datafile
Date: Thu, 7 Apr 2011 10:27:50 +0000
Message-ID: <09011014EB621E4CBC2536B62A1B640702FD52_at_smxc002.trivadis.com>
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-lReceived on Thu Apr 07 2011 - 05:27:50 CDT