(wrong string) …

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Sat, 26 Jun 2010 11:07:51 -0700 (PDT)
Message-ID: <a36a6646-91cf-488f-aeef-972dd4e11daa_at_b35g2000yqi.googlegroups.com>

On Jun 25, 8:58 am, zigzagdna <zigzag..._at_yahoo.com> wrote:
> I have setup  sql  plan management using  sqlmanagement base. Set
> following parameters to control space:
> exec dbms_spm.configure('plan_retention_weeks', 5);
> exec DBMS_SPM.configure('space_budget_percent', 5);
>
> Second parameter as far as I know only gives warning messages in alert
> log, but does not reduce space.
>  First parameter I found cannot be set lower than 5 weeks. Is there
> any way to reduce it to purge space?
> Is there anyway to purge plans?
>
> My sysaux tablespace keeps growing, it is 11GB now. Most of the space
> is occupied by SQL Management Base. How can I free up this space?
> Fri Jun 25 08:33:32 2010
> SQL Management Base Schema
> 9089.9375
> SYS
>
> Server Manageability - Automatic Workload Repository
> 321.1875
> SYS
>
> Enterprise Manager Repository
> 182.6875
> SYSMAN
>
> XDB
> 97.25
> XDB
>
> Server Manageability - Advisor Framework
> 80.1875
> SYS
>
> Server Manageability - Optimizer Statistics History
> 28.8125
> SYS
>
> Oracle Spatial
> 22.75
> MDSYS
>
> Oracle interMedia ORDSYS Components
> 13.8125
> ORDSYS
>
> LogMiner
> 7.625
> SYSTEM
> *** number of plans ***
>     732467

The SQL Management Base Schema size can be controlled via the dbms_spm.configure procedure.

Oracle support note "General Guidelines for SYSAUX Space Issues #552880.1" may also be of use.

This same question was posted to OTN:
http://forums.oracle.com/forums/message.jspa?messageID=4388072#4388072

HTH -- Mark D Powell -- Received on Sat Jun 26 2010 - 13:07:51 CDT