Re: sysaux tablespace growing after database upgrade to 12.2

i agree. Hoeever, in this particular case, it is possible to purge space:

SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor (Doc ID 2305512.1)

It includes simple things we do every day, like re-initializing DBMS_STATS package, rebuilding data dictionary indexes, truncating the dictionary tables and manually run DML (INSERT) on the dictionary table. Nothing out of ordinary that would cause the DBA to worry.

On 2/15/19 9:12 AM, Mark W. Farnham wrote:
>
> The fundamental missing feature is the ability to redirect sysaux
> objects to other tablespaces.
>
> Separation of SYSTEM from SYSAUX was at my insistence with the “Oracle
> VLDB’s” concurrence. Allowing specific growth oriented components to
> be distributed to other tablespaces that could be archived and
> truncated cheaply would solve your problem.
>
> That ability can be finessed, but it is not supported.
>
> mwf
>
> *From:*oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Jeffrey Beckstrom
> *Sent:* Friday, February 15, 2019 8:18 AM
> *To:* oracle-l-freelist; gogala.mladen_at_gmail.com
> *Subject:* Re: sysaux tablespace growing after database upgrade to 12.2
>
> For a SR we have open with Oracle, the analyst is telling us that
> there is no purge of the data and to follow a procedure to drop and
> rebuild the tables and to then disable the process except for when needed.
>
> >>> Mladen Gogala <gogala.mladen_at_gmail.com
> <mailto:gogala.mladen_at_gmail.com>> 2/14/19 10:33 PM >>>
>
> >
>
> Well, DBMS_STATS advisor is very verbose and produces a recommendation
> on each table in the database. My approach is to run the task manually
> and implement recommendations. Unfortunately, there is no mechanism
> which would automatically implement the recommendations.
>
> Regards
>
> On 2/13/19 9:54 AM, Noveljic Nenad wrote:
>
> I haven’t explored other options.
>
> *From:*Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
> <mailto:jbeckstrom_at_gcrta.org>
> *Sent:* Mittwoch, 13. Februar 2019 15:53
> *To:* oracle-l-freelist <oracle-l_at_freelists.org>
> <mailto:oracle-l_at_freelists.org>; Noveljic Nenad
> <nenad.noveljic_at_vontobel.com> <mailto:nenad.noveljic_at_vontobel.com>
> *Subject:* RE: sysaux tablespace growing after database upgrade to
> 12.2
>
> No.
>
> We did not disable the advisor. We would like the advisor to run,
> but not to keep so many rows. Is only solution to delete the advisor?
>
> >>> Noveljic Nenad <nenad.noveljic_at_vontobel.com
> <mailto:nenad.noveljic_at_vontobel.com>> 2/13/19 9:23 AM >>>
>
> Have you disabled the advisor?
>
> DECLARE filter1 CLOB;
>
> BEGIN
>
> filter1 :=
> DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER('AUTO_STATS_ADVISOR_TASK','EXECUTE',NULL,'DISABLE');
>
>
> END;
>
> /
>
> It has worked for me.
>
> Nenad
>
> https://nenadnoveljic.com/blog/
>
> *From:*oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>> *On Behalf Of *Jeffrey
> Beckstrom
> *Sent:* Mittwoch, 13. Februar 2019 14:53
> *To:* oracle-l-freelist <oracle-l_at_freelists.org
> <mailto:oracle-l_at_freelists.org>>
> *Subject:* sysaux tablespace growing after database upgrade to 12.2
>
> Our SYSAUX tablespace is growing following a database upgrade from
> 11.2.0.4 to 12.2.0.1 in mid-December. We matched MOS notes
> 2305512.1 and 2305512.1. We followed instructions in 2439129.1.
>
> However, below query still showed an increase in objects for the
> AUTO_STATS_ADVISOR_TASK task from yesterday's values.
> /*
> from note 2305512.1
> <https://support.oracle.com/epmos/faces/DocumentDisplay?parent=SrDetailText&sourceId=3-19410576981&id=2305512.1>
>
> */
>
> COL TASK_NAME FORMAT A35
> SELECT TASK_NAME, COUNT(*) CNT
> FROM DBA_ADVISOR_OBJECTS
> GROUP BY TASK_NAME
> ORDER BY CNT DESC;
>
> Does something else need to be done?
>
>
>
>
>
> Jeffrey Beckstrom
> Lead Database Administrator
>
> Information Technology Department
>
> Greater Cleveland Regional Transit Authority
>
> 1240 W. 6th Street
> Cleveland, Ohio 44113
>
> ____________________________________________________
>
> Please consider the environment before printing this e-mail.
>
> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>
>
> Important Notice
>
> This message is intended only for the individual named. It may
> contain confidential or privileged information. If you are not the
> named addressee you should in particular not disseminate,
> distribute, modify or copy this e-mail. Please notify the sender
> immediately by e-mail, if you have received this message by
> mistake and delete it from your system.
> Without prejudice to any contractual agreements between you and us
> which shall prevail in any case, we take it as your authorization
> to correspond with you by e-mail if you send us messages by
> e-mail. However, we reserve the right not to execute orders and
> instructions transmitted by e-mail at any time and without further
> explanation.
> E-mail transmission may not be secure or error-free as information
> could be intercepted, corrupted, lost, destroyed, arrive late or
> incomplete. Also processing of incoming e-mails cannot be
> guaranteed. All liability of Vontobel Holding Ltd. and any of its
> affiliates (hereinafter collectively referred to as "Vontobel
> Group") for any damages resulting from e-mail use is excluded. You
> are advised that urgent and time sensitive messages should not be
> sent by e-mail and if verification is required please request a
> printed version.
> Please note that all e-mail communications to and from the
> Vontobel Group are subject to electronic storage and review by
> Vontobel Group. Unless stated to the contrary and without
> prejudice to any contractual agreements between you and Vontobel
> Group which shall prevail in any case, e-mail-communication is for
> informational purposes only and is not intended as an offer or
> solicitation for the purchase or sale of any financial instrument
> or as an official confirmation of any transaction.
> The legal basis for the processing of your personal data is the
> legitimate interest to develop a commercial relationship with you,
> as well as your consent to forward you commercial communications.
> You can exercise, at any time and under the terms established
> under current regulation, your rights. If you prefer not to
> receive any further communications, please contact your client
> relationship manager if you are a client of Vontobel Group or
> notify the sender. Please note for an exact reference to the
> affected group entity the corporate e-mail signature. For further
> information about data privacy at Vontobel Group please consult
> www.vontobel.com <https://www.vontobel.com>.
>
>
> Important Notice
>
> This message is intended only for the individual named. It may
> contain confidential or privileged information. If you are not the
> named addressee you should in particular not disseminate,
> distribute, modify or copy this e-mail. Please notify the sender
> immediately by e-mail, if you have received this message by
> mistake and delete it from your system.
> Without prejudice to any contractual agreements between you and us
> which shall prevail in any case, we take it as your authorization
> to correspond with you by e-mail if you send us messages by
> e-mail. However, we reserve the right not to execute orders and
> instructions transmitted by e-mail at any time and without further
> explanation.
> E-mail transmission may not be secure or error-free as information
> could be intercepted, corrupted, lost, destroyed, arrive late or
> incomplete. Also processing of incoming e-mails cannot be
> guaranteed. All liability of Vontobel Holding Ltd. and any of its
> affiliates (hereinafter collectively referred to as "Vontobel
> Group") for any damages resulting from e-mail use is excluded. You
> are advised that urgent and time sensitive messages should not be
> sent by e-mail and if verification is required please request a
> printed version.
> Please note that all e-mail communications to and from the
> Vontobel Group are subject to electronic storage and review by
> Vontobel Group. Unless stated to the contrary and without
> prejudice to any contractual agreements between you and Vontobel
> Group which shall prevail in any case, e-mail-communication is for
> informational purposes only and is not intended as an offer or
> solicitation for the purchase or sale of any financial instrument
> or as an official confirmation of any transaction.
> The legal basis for the processing of your personal data is the
> legitimate interest to develop a commercial relationship with you,
> as well as your consent to forward you commercial communications.
> You can exercise, at any time and under the terms established
> under current regulation, your rights. If you prefer not to
> receive any further communications, please contact your client
> relationship manager if you are a client of Vontobel Group or
> notify the sender. Please note for an exact reference to the
> affected group entity the corporate e-mail signature. For further
> information about data privacy at Vontobel Group please consult
> www.vontobel.com <https://www.vontobel.com>.
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217



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