You are on an 11g/12c Oracle Database and wish to reduce the usage of space in the SYSAUX tablespace dedicated to the SQL Management Base (SMB), which is where SQL Plan Baselines are stored. Note that this space usage is visible in V$SYSAUX_OCCUPANTS:
Solution:
- First, you must delete any unwanted SQL Plan Baselines from the SMB.How to choose which ones to delete is beyond the scope of this article, but some methods include setting the plan_retention_weeks setting with DBMS_SPM.CONFIGURE or using DBMS_SPM.DROP_SQL_PLAN_BASELINE to delete baselines of your choice found in DBA_SQL_PLAN_BASELINES.
- Run the following script AS SYSDBA (this script can generate micro locks caused by shrink)
alter table "SYS"."SQL$" enable row movement;
alter table "SYS"."SQL$TEXT" enable row movement;
alter table "SYS"."SQLOBJ$AUXDATA" enable row movement;alter table “SYS”.”SQL$” shrink space cascade;
alter table “SYS”.”SQL$TEXT” shrink space cascade;
alter table “SYS”.”SQLOBJ$AUXDATA” shrink space cascade;alter table “SYS”.”SQL$” disable row movement;
alter table “SYS”.”SQL$TEXT” disable row movement;
alter table “SYS”.”SQLOBJ$AUXDATA” disable row movement;alter table “SYS”.”SQLOBJ$” shrink space cascade;
alter table “SYS”.”SQLOBJ$DATA” shrink space cascade;alter table "SYS"."SQL$" modify lob ("SPARE2") (shrink space cascade);
alter table "SYS"."SQL$TEXT" modify lob ("SPARE2") (shrink space cascade);
alter table "SYS"."SQL$TEXT" modify lob ("SQL_TEXT") (shrink space cascade);
alter table "SYS"."SQLOBJ$" modify lob ("SPARE2") (shrink space cascade);
alter table "SYS"."SQLOBJ$DATA" modify lob ("COMP_DATA") (shrink space cascade);
alter table "SYS"."SQLOBJ$DATA" modify lob ("SPARE2") (shrink space cascade);
alter table "SYS"."SQLOBJ$AUXDATA" modify lob ("SPARE2") (shrink space cascade);
Note: Running this script may produce heavy update load on the SYSAUX tablespace. We recommend trying the procedure in a test environment before using it in production. It may also be preferable to run the procedure during off hours.