This discussion is archived
7 Replies Latest reply: Jan 15, 2013 4:45 PM by jgarry RSS

SQL_MANAGEMENT_BASE occupying space in sysaux with no plan baselines inside

961833 Newbie
Currently Being Moderated
select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SYSAUX contributors
SELECT
  occupant_name,
  round( space_usage_kbytes/1024) "Space (M)",
  schema_name,
  move_procedure
FROM
  v$sysaux_occupants where occupant_name='SQL_MANAGEMENT_BASE';

OCCUPANT_NAME                                       Space (M) SCHEMA_NAME          MOVE_PROCEDURE
-------------------------------------------------- ---------- -------------------- ----------------------------------------------------------------
SQL_MANAGEMENT_BASE                                     89770 SYS
'plan_retention_weeks' and 'space_budget_percent' was already set to 5 a month ago.
select  sum(bytes/1024/1024/1024) "Total Size in GB" from dba_data_files where tablespace_name='SYSAUX';
Total Size in GB
----------------
      104.136108
select  sum(bytes/1024/1024/1024) "FREE SPACE in GB" from dba_free_space where tablespace_name='SYSAUX'; 
FREE SPACE in GB
----------------
      6.52612305
SQL baseline details:
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     FALSE

select count(*) from dba_sql_plan_baselines;
  COUNT(*)
----------
         0
select count(*) from dba_sql_profiles;
  COUNT(*)
----------
        0
What is causing such 89770 MB (~87 GB) inside SQL_MANAGEMENT_BASE and why Oracle is not releasing it ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points