Re: Resize SYSAUX datafile

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Thu, 7 Apr 2011 22:49:48 +0200
Message-ID: <BANLkTi=xBRvh0K0eY8sBs68cwrDU0ssfVQ_at_mail.gmail.com>

Well XDB and CTXSYS you could theoretically deinstall and reinstall. Though check this on a test system first to see what it does to your environment / application functionality.

EXFSYS is the expression filter. Are you using that ? If not, there's a deinstall script in ?/rdbms/admin/catnoexf.sql

I also see you mentioning SYSMAN - are you actually using the DBConsole ? If not, simply drop the user. Otherwise you can also consider deinstalling and reinstalling it (though you will loose some historical data).

To work around the LONG issues you can use sql*plus copy, truncate and copy the data back afterwards. But I would check with Oracle first if it's supported to do this on the objects in question.

On the other hand, it seems to me that the database would benefit most from a clean setup. When I encounter databases with this many options it usually originates from a "next - next - next - ... " - install done by someone that didn't know any better, and just installed every option there is, even though none are really used. Nowadays you can quite rapidly re-create a database by using transportable tablespace export / import.

Have fun :)

Stefan

---

Stefan P Knecht
CEO & Founder
s_at_10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch

---

2011/4/7 Ricard Martínez <ricard.martinez_at_gmail.com>

> Hi
>
> 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

Received on Thu Apr 07 2011 - 15:49:48 CDT