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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jeffrey Beckstrom
*Sent:* Friday, February 15, 2019 8:18 AM
*To:* oracle-l-freelist; gogala.mladen@xxxxxxxxx
*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@xxxxxxxxx <mailto:gogala.mladen@xxxxxxxxx>> 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@xxxxxxxxx>
<mailto:jbeckstrom@xxxxxxxxx>
*Sent:* Mittwoch, 13. Februar 2019 15:53
*To:* oracle-l-freelist <oracle-l@xxxxxxxxxxxxx>
<mailto:oracle-l@xxxxxxxxxxxxx>; Noveljic Nenad
<nenad.noveljic@xxxxxxxxxxxx> <mailto:nenad.noveljic@xxxxxxxxxxxx>
*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@xxxxxxxxxxxx
<mailto:nenad.noveljic@xxxxxxxxxxxx>> 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@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> *On Behalf Of *Jeffrey
Beckstrom
*Sent:* Mittwoch, 13. Februar 2019 14:53
*To:* oracle-l-freelist <oracle-l@xxxxxxxxxxxxx
<mailto:oracle-l@xxxxxxxxxxxxx>>
*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
