7 Replies Latest reply: Jan 15, 2013 6:45 PM by jgarry RSS

    SQL_MANAGEMENT_BASE occupying space in sysaux with no plan baselines inside

    961833
      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 ?