Gunther Oracle APEX und Datenbank Wiki

Problembeschreibung

Die Datendateien des SYSAUX Tablespace verbrauchen überdurchschnittlich viel Platz auf Disk.

Ein Umzug des Unified Audit in einen eigenen Tablespace schafft nur wenig Entlastung.

Eine erste Analyse zeigt: 80% des belegten Platzes entfallen auf die Advisor-Infrastruktur der Datenbank wie akkumulierte Advisor-Tasks und die Optimizer-Statistics-History.

Aufgabe:

• Welche Advisor sind aktiv und welcher verursacht den hohen Platzbedarf?
• Wie kann der Platz im SYSAUX bereinigt werden?
• Wie werden die Logdaten der Advisor bereinigt?
• Wie werden die Advisor so konfiguriert, dass der Platzbedarf dauerhaft kontrollierbar bleibt?

SYSAUX durch Advisor-Daten zu voll: Das sind die 5 Sofortmaßnahmen

1. Analyse:V$SYSAUX_OCCUPANTS zeigt welche Komponente wieviel Platz belegt
   1. SM/AWR und SM/ADVISOR sind fast immer die Hauptverursacher.
2. AWR bereinigen: Alte Snapshots mit DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE löschen, danach Retention dauerhaft auf 30 Tage setzen.
3. Advisor-Tasks bereinigen: Akkumulierte COMPLETED/ERROR-Tasks mit DBMS_ADVISOR.DELETE_TASK löschen — auf Produktionsdatenbanken ohne automatische Task-Bereinigung sammeln sich schnell tausende Tasks an.
4. Stats-History kürzen: DBMS_STATS.PURGE_STATS + Retention auf 14–21 Tage reduzieren.
5. SPM Evolve Advisor prüfen: SQL Management Base (SMB) belegt per Default bis zu 10% des SYSAUX. Plan-Retention von 53 Wochen auf 26 Wochen reduzieren und ungenutzte Baselines bereinigen(DBMS_SPM.CONFIGURE + DBMS_SPM.DROP_SQL_PLAN_BASELINE).
6. Dauerhaft absichern: Wöchentlichen Scheduler-Job für die Bereinigung einrichten und das SYSAUX-Wachstum im Monitoring (OEM) als Alert hinterlegen.

SPM beachten! — Der wird in der Praxis am häufigsten vergessen, weil SPM im Gegensatz zu AWR und ADDM keinen eigenen V$SYSAUX_OCCUPANTS-Eintrag hat und der Default von 53 Wochen Retention in keiner Standard-Installationsanleitung erwähnt wird.

Vorab Größe Report AWR Aufrufen:

@?/rdbms/admin/awrinfo.sql

Oracle liefert eine Reihe von eingebauten Advisors mit, die im Hintergrund laufen und Empfehlungen zur Performance, Speicher, SQL-Tuning und Wartung generieren.

Ihre Ergebnisse und Zwischendaten werden fast ausnahmslos im SYSAUX-Tablespace abgelegt.

Die aktiven Advisor-Jobs lassen sich über die AutoTask-Infrastruktur (DBMS_AUTO_TASK_ADMIN) sowie über DBA_ADVISOR_TASKS einsehen.

Welche Advisor-Tasks sind aktuell registriert?

Überblick aller Advisor Tasks:

SELECT
    CLIENT_NAME, STATUS, CLIENT_TAG, WINDOW_GROUP, -- 26ai? AUTOTASK_STATUS
FROM
    dba_autotask_client
ORDER BY
    client_name;

Alle Advisor Tasks in der Datenbank (manuelle + automatische):

SELECT
    owner,
    task_name,
    advisor_name,
    STATUS,
    created,
    last_modified,
    description
FROM
    dba_advisor_tasks
ORDER BY
    advisor_name,
    created DESC
FETCH FIRST 50 ROWS ONLY;

Summary:

SELECT  advisor_name,
     STATUS,
     MIN(CREATED) AS min_CREATED,
     MAX(CREATED) AS max_CREATED,
     COUNT(*) AS cnt       
FROM
    dba_advisor_tasks
GROUP BY advisor_name,STATUS
ORDER BY  5 DESC

Die drei größten Platzkonsumenten im SYSAUX sind in der Regel:

1. AWR (SM/AWR) — Snapshots wachsen proportional zu Datenbankaktivität × Retention
2. SQL Tuning Advisor / Automatic SQL Tuning (SM/ADVISOR) — akkumuliert unbereinigt über Monate
3. Optimizer Statistics Advisor — Findings-History mit hoher Schreibfrequenz

-- Platzbedarf je SYSAUX-Komponente (Überblick)
SELECT
    occupant_name,
    occupant_desc,
    round(space_usage_kbytes / 1024, 2)  AS space_mb,
    schema_name,
    move_procedure
FROM
    v$sysaux_occupants
ORDER BY
    space_usage_kbytes DESC;

-- Detailblick: Welche Advisor-Task-Daten belegen wieviel Platz?
SELECT
    owner,
    segment_name,
    segment_type,
    round(bytes / 1024 / 1024, 2) AS size_mb
FROM
    dba_segments
WHERE
    tablespace_name = 'SYSAUX'
    AND ( segment_name LIKE 'WRI$_ADVISOR%'
          OR segment_name LIKE 'WRM$%'
          OR segment_name LIKE 'WRH$%' )
ORDER BY
    bytes DESC
FETCH FIRST 30 ROWS ONLY;

Bevor Platz bereinigt oder Advisor-Konfigurationen geändert werden, muss klar sein welche Komponente wie viel Platz belegt und warum der Verbrauch so hoch ist. Die Analyse erfolgt in drei Ebenen: Tablespace-Ebene → Komponenten-Ebene → Objekt-Ebene.

-- Gesamtgröße und freier Platz im SYSAUX Tablespace
SELECT
    df.tablespace_name,
    round(df.bytes / 1024 / 1024, 2)              AS total_mb,
    round(( df.bytes - nvl(fs.bytes, 0) ) / 1024 / 1024, 2) AS used_mb,
    round(nvl(fs.bytes, 0) / 1024 / 1024, 2)      AS free_mb,
    round(nvl(fs.bytes, 0) / df.bytes * 100, 2)   AS free_pct
FROM
    (
        SELECT
            tablespace_name,
            SUM(bytes) AS bytes
        FROM
            dba_data_files
        GROUP BY
            tablespace_name
    ) df
    LEFT JOIN (
        SELECT
            tablespace_name,
            SUM(bytes) AS bytes
        FROM
            dba_free_space
        GROUP BY
            tablespace_name
    ) fs ON df.tablespace_name = fs.tablespace_name
WHERE
    df.tablespace_name = 'SYSAUX'
ORDER BY
    df.tablespace_name;

Die View V$SYSAUX_OCCUPANTS liefert den aktuell belegten Platz je Komponente sowie die zugehörige MOVE_PROCEDURE für einen eventuellen Umzug.

-- Alle SYSAUX-Komponenten sortiert nach Platzverbrauch
SELECT
    occupant_name,
    occupant_desc,
    schema_name,
    round(space_usage_kbytes / 1024, 2)        AS space_mb,
    round(space_usage_kbytes / 1024 / 1024, 2) AS space_gb,
    move_procedure
FROM
    v$sysaux_occupants
ORDER BY
    space_usage_kbytes DESC;

Typische Schwergewichte die auf Advisor-Aktivität hindeuten:

-- Top-20 AWR-Segmente nach Größe
SELECT
    segment_name,
    segment_type,
    round(bytes / 1024 / 1024, 2) AS size_mb
FROM
    dba_segments
WHERE
    tablespace_name = 'SYSAUX'
    AND owner = 'SYS'
    AND ( segment_name LIKE 'WRH$%'
          OR segment_name LIKE 'WRM$%'
          OR segment_name LIKE 'WRI$%' )
ORDER BY
    bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- Advisor-spezifische Segmente — welcher Advisor hinterlässt die meisten Daten?
SELECT
    s.segment_name,
    s.segment_type,
    round(s.bytes / 1024 / 1024, 2) AS size_mb
FROM
    dba_segments s
WHERE
    s.tablespace_name = 'SYSAUX'
    AND s.owner = 'SYS'
    AND s.segment_name LIKE 'WRI$_ADVISOR%'
ORDER BY
    s.bytes DESC;

-- Optimizer Statistics History — oft unterschätzter Platzfresser
SELECT
    segment_name,
    segment_type,
    round(bytes / 1024 / 1024, 2) AS size_mb
FROM
    dba_segments
WHERE
    tablespace_name = 'SYSAUX'
    AND owner = 'SYS'
    AND segment_name LIKE 'WRI$_OPTSTAT%'
ORDER BY
    bytes DESC;

Die AWR-Retention ist der wichtigste Stellhebel für den SYSAUX-Platzbedarf. Je länger die Retention, desto mehr Snapshot-Daten akkumulieren sich.

-- Aktuelle AWR-Konfiguration
SELECT
    dbid,
    snap_interval,
    retention,
    topnsql,
    con_id
FROM
    dba_hist_wr_control;

Standardwerte und ihre Auswirkungen:

-- Wie viele AWR-Snapshots sind aktuell gespeichert?
SELECT
    COUNT(*)                                            AS snapshot_count,
    MIN(begin_interval_time)                            AS oldest_snapshot,
    MAX(end_interval_time)                              AS newest_snapshot,
    round(
        ( CAST(MAX(end_interval_time) AS DATE)
          - CAST(MIN(begin_interval_time) AS DATE) ), 1
    )                                                   AS retention_days
FROM
    dba_hist_snapshot;

-- Wachstumstrend der AWR-Snapshots: Platz je Snapshot-Tag
SELECT
    trunc(begin_interval_time, 'DD')  AS snap_day,
    COUNT(*)                          AS snapshots_per_day,
    MIN(snap_id)                      AS first_snap,
    MAX(snap_id)                      AS last_snap
FROM
    dba_hist_snapshot
GROUP BY
    trunc(begin_interval_time, 'DD')
ORDER BY
    snap_day DESC
FETCH FIRST 14 ROWS ONLY;

Advisor-Tasks werden standardmäßig nicht automatisch gelöscht. Über Monate oder Jahre wächst die Tabelle WRI$_ADVISOR_TASKS unkontrolliert.

-- Überblick: Anzahl Tasks je Advisor und Status
SELECT
    advisor_name,
    STATUS,
    COUNT(*)           AS task_count,
    MIN(created)       AS oldest_task,
    MAX(last_modified) AS newest_task
FROM
    dba_advisor_tasks
GROUP BY
    advisor_name,
    STATUS
ORDER BY
    task_count DESC;

-- Automatisch erzeugte Tasks (AutoTask-Runs) der letzten 30 Tage
SELECT
    advisor_name,
    COUNT(*) AS auto_tasks_30d
FROM
    dba_advisor_tasks
WHERE
    created >= sysdate - 30
    AND description LIKE '%auto%'
GROUP BY
    advisor_name
ORDER BY
    auto_tasks_30d DESC;

-- Älteste Tasks je Advisor — Kandidaten für Bereinigung
SELECT
    advisor_name,
    task_name,
    STATUS,
    created,
    last_modified,
    round(sysdate - created) AS age_days
FROM
    dba_advisor_tasks
WHERE
    created < sysdate - 30
ORDER BY
    created ASC
FETCH FIRST 30 ROWS ONLY;

Das folgende Script liefert eine kompakte Übersicht aller relevanten Kennzahlen in einem einzigen Lauf — geeignet als Ausgangsbasis für das Ticket oder den Bericht:

check_advisor.sql

-- ============================================================
-- SYSAUX Advisor Space Report
-- Zweck  : Schnellanalyse SYSAUX-Platzbedarf durch Advisor Jobs
-- Autor  : G. Pipperr
-- Version: 1.0
-- ============================================================
SET LINESIZE 200
SET PAGESIZE 100
SET FEEDBACK OFF
 
PROMPT =============================================
PROMPT SYSAUX: Gesamtverbrauch
PROMPT =============================================
 
SELECT
    round(SUM(bytes) / 1024 / 1024, 2) AS total_sysaux_mb
FROM
    dba_data_files
WHERE
    tablespace_name = 'SYSAUX';
 
PROMPT =============================================
PROMPT SYSAUX: Top-10 Komponenten
PROMPT =============================================
 
SELECT * FROM (
    SELECT
        occupant_name,
        round(space_usage_kbytes / 1024, 2) AS space_mb,
        move_procedure
    FROM
        v$sysaux_occupants
    ORDER BY
        space_usage_kbytes DESC
)
FETCH FIRST 10 ROWS ONLY;
 
PROMPT =============================================
PROMPT AWR: Retention und Snapshot-Anzahl
PROMPT =============================================
 
SELECT
    retention,
    snap_interval,
    topnsql
FROM
    dba_hist_wr_control;
 
SELECT
    COUNT(*)                                          AS total_snapshots,
    round(
        CAST(MAX(end_interval_time) AS DATE)
        - CAST(MIN(begin_interval_time) AS DATE), 1
    )                                                 AS retention_days_actual
FROM
    dba_hist_snapshot;
 
PROMPT =============================================
PROMPT ADVISOR: Task-Akkumulation je Advisor
PROMPT =============================================
 
SELECT
    advisor_name,
    COUNT(*)      AS total_tasks,
    MIN(created)  AS oldest_task
FROM
    dba_advisor_tasks
GROUP BY
    advisor_name
ORDER BY
    total_tasks DESC;
 
SET FEEDBACK ON

Die Tabelle WRI$_ADV_OBJECTS ist in vielen Datenbanken das größte einzelne Objekt im SYSAUX — verursacht durch den AUTO_STATS_ADVISOR_TASK der in Oracle 12.2 mit EXECUTION_DAYS_TO_EXPIRE = UNLIMITED ausgeliefert wurde. Das bedeutet: Einträge werden nie automatisch gelöscht.

Quelle ⇒ https://support.oracle.com/support/?anchorId=&kmContentId=2660128&page=sptemplate&sptemplate=km-article

-- Retention-Parameter des Optimizer Statistics Advisors prüfen
-- UNLIMITED = Bug aktiv oder nie konfiguriert!
SELECT
    task_name,
    parameter_name,
    parameter_value
FROM
    dba_advisor_parameters
WHERE
    task_name    IN ( 'AUTO_STATS_ADVISOR_TASK', 'INDIVIDUAL_STATS_ADVISOR_TASK' )
    AND parameter_name = 'EXECUTION_DAYS_TO_EXPIRE';

-- Wie viele Executions akkumuliert? Älteste anzeigen
SELECT
    task_id,
    task_name,
    execution_name,
    execution_start
FROM
    dba_advisor_executions
WHERE
    task_name = 'AUTO_STATS_ADVISOR_TASK'
ORDER BY
    execution_start ASC
FETCH FIRST 10 ROWS ONLY;

-- EXECUTION_DAYS_TO_EXPIRE auf 10 Tage setzen
-- (Default nach Bugfix-Patch: 30 Tage)
EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
    task_name => 'AUTO_STATS_ADVISOR_TASK',
    parameter => 'EXECUTION_DAYS_TO_EXPIRE',
    VALUE     => 10
);
 
-- Gleiches für den manuellen Advisor-Task falls vorhanden
EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
    task_name => 'INDIVIDUAL_STATS_ADVISOR_TASK',
    parameter => 'EXECUTION_DAYS_TO_EXPIRE',
    VALUE     => 10
);

-- Schnellster Weg: alle expired Tasks in einem Aufruf löschen
-- (schneller als die Task-by-Task-Schleife mit DBMS_ADVISOR.DELETE_TASK)
EXEC prvt_advisor.delete_expired_tasks;

Bei sehr alten Datenbanken mit jahrelang akkumulierten Einträgen kann prvt_advisor.delete_expired_tasks mit ORA-01555 (Snapshot too old) fehlschlagen. In diesem Fall schrittweise vorgehen:

-- ORA-01555-sichere Variante: schrittweise Bereinigung
-- Löscht in 50-Tages-Schritten von 2 Jahren zurück auf 30 Tage
SET SERVEROUTPUT ON
DECLARE
    v_oldest    INTEGER := 730;   -- Start: 2 Jahre zurück
    v_increment INTEGER := 50;    -- Schrittgröße in Tagen
    v_min_age   INTEGER := 30;    -- Minimum-Retention behalten
    v_cur_age   INTEGER;
BEGIN
    v_cur_age := v_oldest;
    WHILE v_cur_age >= v_min_age LOOP
        dbms_output.put_line( 'Bereinige Tasks älter als ' || v_cur_age || ' Tage ...' );
        dbms_sqltune.set_tuning_task_parameter(
            task_name => 'AUTO_STATS_ADVISOR_TASK',
            parameter => 'EXECUTION_DAYS_TO_EXPIRE',
            VALUE     => v_cur_age
        );
        prvt_advisor.delete_expired_tasks;
        v_cur_age := v_cur_age - v_increment;
    END LOOP;
    dbms_output.put_line( 'Bereinigung abgeschlossen.' );
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line( 'Fehler: ' || sqlerrm );
END;
/

Nach dem Purge ist der Platz in WRI$_ADV_OBJECTS logisch frei, der High-Watermark aber noch nicht zurückgesetzt. Ohne diesen Schritt bleibt der SYSAUX-Platzbedarf trotz Bereinigung hoch.

-- Im CDB$ROOT oder Non-CDB direkt ausführen:
ALTER TABLE WRI$_ADV_OBJECTS MOVE;
 
-- Anschließend die drei Indizes neu aufbauen (sonst UNUSABLE!)
ALTER INDEX WRI$_ADV_OBJECTS_PK        REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01    REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_02    REBUILD;

-- In einer PDB: über DBMS_PDB.EXEC_AS_ORACLE_SCRIPT ausführen
EXEC dbms_pdb.exec_as_oracle_script(
    'alter table WRI$_ADV_OBJECTS move parallel 2'
);
 
-- Danach Indizes auch über exec_as_oracle_script rebuilden
EXEC dbms_pdb.exec_as_oracle_script(
    'alter index WRI$_ADV_OBJECTS_PK rebuild'
);
EXEC dbms_pdb.exec_as_oracle_script(
    'alter index WRI$_ADV_OBJECTS_IDX_01 rebuild'
);
EXEC dbms_pdb.exec_as_oracle_script(
    'alter index WRI$_ADV_OBJECTS_IDX_02 rebuild'
);

-- Ergebnis prüfen: Platz in V$SYSAUX_OCCUPANTS gesunken?
SELECT
    occupant_name,
    round(space_usage_kbytes / 1024, 2) AS space_mb
FROM
    v$sysaux_occupants
WHERE
    occupant_name = 'SM/ADVISOR';

-- Konfiguriertes SYSAUX-Platzlimit für die SQL Management Base
-- SPACE_BUDGET_PERCENT : Anteil am SYSAUX in Prozent (Default 10%)
-- PLAN_RETENTION_WEEKS : Retention für unbenutzte Pläne (Default 53 Wochen!)
SELECT
    c.parameter_name,
    c.parameter_value,
    round(
        c.parameter_value / 100
        * ( SELECT SUM(bytes) / 1024 / 1024
            FROM   dba_data_files
            WHERE  tablespace_name = 'SYSAUX' ), 2
    )  AS current_limit_mb
FROM
    dba_sql_management_config c
WHERE
    c.parameter_name IN ( 'SPACE_BUDGET_PERCENT', 'PLAN_RETENTION_WEEKS' );

-- Anzahl SQL Plan Baselines je Status
SELECT
    accepted,
    enabled,
    fixed,
    reproduced,
    COUNT(*)    AS plan_count
FROM
    dba_sql_plan_baselines
GROUP BY
    accepted,
    enabled,
    fixed,
    reproduced
ORDER BY
    plan_count DESC;

-- Älteste und neueste Baselines — Kandidaten für Bereinigung
SELECT
    sql_handle,
    plan_name,
    accepted,
    last_executed,
    created,
    round(sysdate - last_executed) AS days_since_last_use
FROM
    dba_sql_plan_baselines
WHERE
    last_executed < sysdate - 180   -- seit 6 Monaten nicht genutzt
ORDER BY
    last_executed ASC
FETCH FIRST 20 ROWS ONLY;

-- Konfiguration des SYS_AUTO_SPM_EVOLVE_TASK prüfen
SELECT
    parameter_name,
    parameter_value
FROM
    dba_advisor_parameters
WHERE
    task_name     = 'SYS_AUTO_SPM_EVOLVE_TASK'
    AND parameter_value != 'UNUSED'
ORDER BY
    parameter_name;

-- Plan-Retention von 53 Wochen auf 26 Wochen reduzieren
-- Damit werden unbenutzte Pläne früher automatisch bereinigt
BEGIN
    dbms_spm.configure(
        parameter_name  => 'PLAN_RETENTION_WEEKS',
        parameter_value => 26
    );
END;
/

-- SYSAUX-Platzlimit für SMB von 10% auf 5% reduzieren
-- (sinnvoll wenn SYSAUX generell klein ist)
BEGIN
    dbms_spm.configure(
        parameter_name  => 'SPACE_BUDGET_PERCENT',
        parameter_value => 5
    );
END;
/

-- Nicht-akzeptierte und seit > 180 Tagen ungenutzte Pläne löschen
DECLARE
    v_dropped  PLS_INTEGER;
    v_total    PLS_INTEGER := 0;
BEGIN
    FOR r IN (
        SELECT sql_handle, plan_name
        FROM   dba_sql_plan_baselines
        WHERE  accepted     = 'NO'
               AND ( last_executed IS NULL
                     OR last_executed < sysdate - 180 )
    ) LOOP
        v_dropped := dbms_spm.drop_sql_plan_baseline(
            sql_handle => r.sql_handle,
            plan_name  => r.plan_name
        );
        v_total := v_total + v_dropped;
    END LOOP;
    dbms_output.put_line( 'Gelöschte Baselines: ' || v_total );
END;
/

-- Nur relevant wenn der High-Frequency-Modus aktiv ist (ADB / 26ai)
-- und zu viele Tasks außerhalb des Maintenance-Fensters generiert werden
BEGIN
    dbms_spm.configure(
        parameter_name  => 'AUTO_SPM_EVOLVE_TASK',
        parameter_value => 'OFF'
    );
END;
/
 
-- Wieder auf Standard (Maintenance-Window-Only) zurücksetzen
BEGIN
    dbms_spm.configure(
        parameter_name  => 'AUTO_SPM_EVOLVE_TASK',
        parameter_value => 'AUTO'   -- 19c Standard
    );
END;
/

Quellen:

• 26ai: SQL Tuning Guide — Managing SQL Plan Baselines (SPM Evolve Advisor)
• 26ai: PL/SQL Reference — DBMS_SPM
• Oracle Optimizer Blog — Automatic SQL Plan Management (Nigel Bayliss)

Nach der Analyse aus Abschnitt 2 ist klar, welche Komponente den SYSAUX-Tablespace füllt. Die Bereinigung erfolgt immer in dieser Reihenfolge:

1. AWR-Snapshots reduzieren (größter Hebel)
2. Advisor-Tasks löschen (akkumulierte Task-History)
3. Optimizer Statistics History kürzen
4. Speicher physisch zurückgewinnen (Shrink)

Wenn ein Advisor über Monate oder Jahre gelaufen ist, seine Ergebnisse aber nie systematisch ausgewertet wurden, ist der schnellste Weg zur Platzrückgewinnung:

1. Advisor deaktivieren
2. Alle akkumulierten Task-Daten löschen
3. Advisor mit sauberer Konfiguration neu starten

Das ist kein Notfallhack sondern eine legitime Betriebsentscheidung: Wer die Empfehlungen eines Advisors nicht liest, zahlt trotzdem den Preis für die Datenhaltung im SYSAUX.

Der SQL Tuning Advisor ist der häufigste Platzkonsument in SM/ADVISOR und der sinnvollste Kandidat für diesen Ansatz.

-- Platzbedarf und Task-Anzahl vor der Aktion festhalten
SELECT
    occupant_name,
    round(space_usage_kbytes / 1024, 2) AS space_mb
FROM
    v$sysaux_occupants
WHERE
    occupant_name IN ( 'SM/ADVISOR', 'SM/AWR', 'SM/OPTSTAT' );

-- Wie viele SQL Tuning Advisor Tasks sind akkumuliert?
SELECT
    advisor_name,
    STATUS,
    COUNT(*)          AS task_count,
    MIN(created)      AS oldest_task,
    MAX(created)      AS newest_task
FROM
    dba_advisor_tasks
WHERE
    advisor_name = 'SQL Tuning Advisor'
GROUP BY
    advisor_name,
    STATUS
ORDER BY
    task_count DESC;

-- SQL Tuning Advisor AutoTask abschalten
-- Ab diesem Moment werden keine neuen Tasks mehr erzeugt
BEGIN
    dbms_auto_task_admin.disable(
        client_name => 'sql tuning advisor',
        operation   => NULL,
        window_name => NULL
    );
END;
/

-- Prüfen ob der Job wirklich deaktiviert ist
SELECT
    client_name,
    STATUS
FROM
    dba_autotask_client
WHERE
    client_name = 'sql tuning advisor';
-- Erwartete Ausgabe: STATUS = DISABLED

-- Alle SQL Tuning Advisor Tasks löschen (außer laufenden)
-- Das gibt den Platz in WRI$_ADVISOR_* frei
DECLARE
    v_deleted  NUMBER := 0;
    v_skipped  NUMBER := 0;
BEGIN
    FOR r IN (
        SELECT task_name, STATUS
        FROM   dba_advisor_tasks
        WHERE  advisor_name = 'SQL Tuning Advisor'
               AND STATUS  != 'EXECUTING'
    ) LOOP
        BEGIN
            dbms_advisor.delete_task( task_name => r.task_name );
            v_deleted := v_deleted + 1;
        EXCEPTION
            WHEN OTHERS THEN
                v_skipped := v_skipped + 1;
        END;
    END LOOP;
    COMMIT;
    dbms_output.put_line(
        'Gelöscht: ' || v_deleted
        || ' | Übersprungen: ' || v_skipped
    );
END;
/

High-Watermark der WRI$_ADVISOR_*-Segmente manuell zurücksetzen

Nach dem Löschen der Tasks ist der Platz logisch frei, physisch aber noch belegt. Da ALTER TABLE … SHRINK SPACE auf SYS-eigene Segmente nicht anwendbar ist, gibt es zwei unterstützte Wege:

Weg 1: DBMS_SPACE_ADMIN — Segment Shrink für einzelne Segmente

-- Kandidaten: WRI$_ADVISOR_*-Segmente mit viel freiem Platz identifizieren
SELECT
    segment_name,
    segment_type,
    round(bytes / 1024 / 1024, 2)                          AS allocated_mb,
    round(
        ( bytes - dbms_space.unused_space(
              segment_owner     => 'SYS',
              segment_name      => segment_name,
              segment_type      => segment_type
          )
        ) / 1024 / 1024, 2
    )                                                       AS used_mb_approx
FROM
    dba_segments
WHERE
    tablespace_name = 'SYSAUX'
    AND owner       = 'SYS'
    AND segment_name LIKE 'WRI$_ADVISOR%'
ORDER BY
    bytes DESC
FETCH FIRST 10 ROWS ONLY;

-- Einzelnes Segment shrink (nur für non-SYS-Objekte direkt möglich)
-- Für SYS-Objekte: DBMS_SPACE_ADMIN.SEGMENT_SHRINK verwenden
BEGIN
    dbms_space_admin.segment_shrink(
        schema_name  => 'SYS',
        object_name  => 'WRI$_ADVISOR_TASKS',   -- Segment anpassen
        object_type  => dbms_space_admin.auto_segment_all,
        object_part  => NULL
    );
END;
/

-- Hinweis: V$SYSAUX_OCCUPANTS aktualisiert sich nicht sofort.
-- Einige Minuten warten, dann erneut prüfen.
-- Der High-Watermark der WRI$_ADVISOR_*-Segmente sinkt erst
-- nach einem Shrink oder dem nächsten Segment-Reuse.
SELECT
    occupant_name,
    round(space_usage_kbytes / 1024, 2) AS space_mb_nach_delete
FROM
    v$sysaux_occupants
WHERE
    occupant_name = 'SM/ADVISOR';

-- SQL Tuning Advisor wieder aktivieren
BEGIN
    dbms_auto_task_admin.enable(
        client_name => 'sql tuning advisor',
        operation   => NULL,
        window_name => NULL
    );
END;
/

-- Logging-Level direkt auf BASIC setzen bevor der erste neue Task läuft
BEGIN
    dbms_auto_task_admin.set_client_prop(
        client_name => 'sql tuning advisor',
        prop_name   => 'LOGGING LEVEL',
        prop_value  => 'BASIC'
    );
    COMMIT;
END;
/

-- Status nach dem Neustart prüfen
SELECT
    client_name,
    STATUS
 FROM
    dba_autotask_client
WHERE
    client_name = 'sql tuning advisor';

-- Segment Advisor deaktivieren
BEGIN
    dbms_auto_task_admin.disable(
        client_name => 'auto space advisor',
        operation   => NULL,
        window_name => NULL
    );
END;
/
 
-- Alle Segment Advisor Tasks löschen
DECLARE
    v_deleted NUMBER := 0;
BEGIN
    FOR r IN (
        SELECT task_name
        FROM   dba_advisor_tasks
        WHERE  advisor_name = 'Segment Advisor'
               AND STATUS  != 'EXECUTING'
    ) LOOP
        BEGIN
            dbms_advisor.delete_task( task_name => r.task_name );
            v_deleted := v_deleted + 1;
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;
    COMMIT;
    dbms_output.put_line( 'Segment Advisor Tasks gelöscht: ' || v_deleted );
END;
/
 
-- Segment Advisor neu aktivieren
BEGIN
    dbms_auto_task_admin.enable(
        client_name => 'auto space advisor',
        operation   => NULL,
        window_name => NULL
    );
END;
/

-- Vergleichs-Query: vor dem Reset ausführen und Ergebnis notieren,
-- nach dem Reset erneut ausführen
SELECT
    o.occupant_name,
    round(o.space_usage_kbytes / 1024, 2)  AS space_mb,
    ( SELECT COUNT(*)
      FROM   dba_advisor_tasks t
      WHERE  t.advisor_name LIKE
                 CASE o.occupant_name
                     WHEN 'SM/ADVISOR' THEN '%'
                     ELSE '~~~'
                 END
    )                                       AS total_tasks,
    c.status                                AS autotask_status
FROM
    v$sysaux_occupants o
    LEFT JOIN dba_autotask_client c
           ON c.client_name IN (
                  'sql tuning advisor',
                  'auto space advisor',
                  'auto optimizer stats collection'
              )
WHERE
    o.occupant_name IN ( 'SM/ADVISOR', 'SM/AWR', 'SM/OPTSTAT' )
GROUP BY
    o.occupant_name,
    o.space_usage_kbytes,
    c.status
ORDER BY
    o.space_usage_kbytes DESC;

-- Snapshot-Bereich bestimmen: älteste und neueste Snapshot-ID
SELECT
    MIN(snap_id)            AS oldest_snap_id,
    MAX(snap_id)            AS newest_snap_id,
    MIN(begin_interval_time) AS oldest_date,
    MAX(end_interval_time)   AS newest_date,
    COUNT(*)                AS total_count
FROM
    dba_hist_snapshot
WHERE
    dbid = ( SELECT dbid FROM v$database );

-- Snapshots die älter als die gewünschte neue Retention sind (z.B. 35 Tage)
-- Kandidaten für den DROP_SNAPSHOT_RANGE
SELECT
    snap_id,
    begin_interval_time,
    end_interval_time
FROM
    dba_hist_snapshot
WHERE
    dbid            = ( SELECT dbid FROM v$database )
    AND begin_interval_time < sysdate - 35   -- Neue Ziel-Retention in Tagen
ORDER BY
    snap_id ASC
FETCH FIRST 20 ROWS ONLY;

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE löscht Snapshots inklusive aller abhängigen AWR-Daten (WRH$_*-Tabellen) für den angegebenen Bereich.

-- Einzelnen Snapshot-Bereich löschen
-- low_snap_id : erster zu löschender Snapshot (aus obiger Abfrage)
-- high_snap_id: letzter zu löschender Snapshot
-- dbid        : aus V$DATABASE
BEGIN
    dbms_workload_repository.drop_snapshot_range(
        low_snap_id  => 1,        -- anpassen
        high_snap_id => 4800,     -- anpassen: alle Snapshots älter als Ziel-Retention
        dbid         => ( SELECT dbid FROM v$database )
    );
    COMMIT;
END;
/

-- Schrittweise Bereinigung in Blöcken (Produktions-sicherer Ansatz)
-- Anpassen: v_block_size und v_target_retention_days
DECLARE
    v_dbid                 NUMBER;
    v_block_size           NUMBER := 500;
    v_target_retention_days NUMBER := 35;
    v_low_snap             NUMBER;
    v_high_snap            NUMBER;
    v_cutoff_date          DATE;
 
    CURSOR c_blocks IS
        SELECT
            MIN(snap_id) AS low_id,
            MAX(snap_id) AS high_id
        FROM (
            SELECT
                snap_id,
                CEIL(rownum / v_block_size) AS block_nr
            FROM
                dba_hist_snapshot
            WHERE
                dbid                = v_dbid
                AND begin_interval_time < v_cutoff_date
            ORDER BY
                snap_id
        )
        GROUP BY
            block_nr
        ORDER BY
            MIN(snap_id);
 
BEGIN
    SELECT dbid INTO v_dbid FROM v$database;
    v_cutoff_date := sysdate - v_target_retention_days;
 
    FOR r IN c_blocks LOOP
        dbms_output.put_line(
            'Lösche Snapshots ' || r.low_id || ' bis ' || r.high_id
        );
        dbms_workload_repository.drop_snapshot_range(
            low_snap_id  => r.low_id,
            high_snap_id => r.high_id,
            dbid         => v_dbid
        );
        COMMIT;
        dbms_lock.sleep(2);   -- kurze Pause zwischen den Blöcken
    END LOOP;
 
    dbms_output.put_line('AWR-Bereinigung abgeschlossen.');
END;
/

-- Verbleibende Snapshots nach der Bereinigung
SELECT
    COUNT(*)                  AS remaining_snapshots,
    MIN(begin_interval_time)  AS new_oldest,
    MAX(end_interval_time)    AS newest
FROM
    dba_hist_snapshot
WHERE
    dbid = ( SELECT dbid FROM v$database );

-- Platzbedarf SM/AWR vor und nach dem Shrink (direkt nach DROP noch nicht reduziert)
SELECT
    occupant_name,
    round(space_usage_kbytes / 1024, 2) AS space_mb
FROM
    v$sysaux_occupants
WHERE
    occupant_name IN ( 'SM/AWR', 'SM/ADVISOR', 'SM/OPTSTAT' );

Advisor-Tasks akkumulieren sich über Monate, weil keine automatische Task-Retention konfiguriert ist. Vor dem Löschen genau unterscheiden:

-- Einzelnen Advisor-Task löschen (nach Prüfung)
BEGIN
    dbms_advisor.delete_task( task_name => 'TASK_NAME_HIER' );
    COMMIT;
END;
/

-- Bereinigung aller COMPLETED/ERROR-Tasks des SQL Tuning Advisor
-- die älter als 30 Tage sind
DECLARE
    v_deleted  NUMBER := 0;
    v_errors   NUMBER := 0;
BEGIN
    FOR r IN (
        SELECT task_name
        FROM   dba_advisor_tasks
        WHERE  advisor_name  = 'SQL Tuning Advisor'
               AND STATUS    IN ( 'COMPLETED', 'ERROR', 'INITIAL' )
               AND created   < sysdate - 30
    ) LOOP
        BEGIN
            dbms_advisor.delete_task( task_name => r.task_name );
            v_deleted := v_deleted + 1;
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line(
                    'Fehler bei Task ' || r.task_name || ': ' || sqlerrm
                );
                v_errors := v_errors + 1;
        END;
    END LOOP;
    COMMIT;
    dbms_output.put_line(
        'Gelöscht: ' || v_deleted || ' Tasks | Fehler: ' || v_errors
    );
END;
/

-- Bereinigung aller Advisors in einem Lauf
-- Sicher: nur COMPLETED / ERROR / INITIAL, älter als p_days_to_keep Tage
-- EXECUTING-Tasks werden explizit ausgeschlossen
DECLARE
    v_days_to_keep  NUMBER  := 30;    -- Tasks jünger als X Tage behalten
    v_deleted       NUMBER  := 0;
    v_skipped       NUMBER  := 0;
BEGIN
    FOR r IN (
        SELECT
            task_name,
            advisor_name,
            STATUS,
            created
        FROM
            dba_advisor_tasks
        WHERE
            STATUS   IN ( 'COMPLETED', 'ERROR', 'INITIAL' )
            AND STATUS   != 'EXECUTING'
            AND created  < sysdate - v_days_to_keep
        ORDER BY
            created ASC
    ) LOOP
        BEGIN
            dbms_advisor.delete_task( task_name => r.task_name );
            v_deleted := v_deleted + 1;
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line(
                    'Skip: ' || r.task_name
                    || ' (' || r.advisor_name || ')'
                    || ' -> ' || sqlerrm
                );
                v_skipped := v_skipped + 1;
        END;
    END LOOP;
    COMMIT;
    dbms_output.put_line(
        'Advisor-Tasks bereinigt: '
        || v_deleted || ' gelöscht, '
        || v_skipped || ' übersprungen.'
    );
END;
/

ADDM-Tasks haben ein eigenes Package — DBMS_ADVISOR.DELETE_TASK funktioniert, aber DBMS_ADDM bietet zusätzliche Kontrolle:

-- Alle ADDM-Tasks die älter als 60 Tage sind löschen
DECLARE
    v_deleted NUMBER := 0;
BEGIN
    FOR r IN (
        SELECT task_name
        FROM   dba_advisor_tasks
        WHERE  advisor_name = 'ADDM'
               AND STATUS   IN ( 'COMPLETED', 'ERROR' )
               AND created  < sysdate - 60
    ) LOOP
        BEGIN
            dbms_advisor.delete_task( task_name => r.task_name );
            v_deleted := v_deleted + 1;
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;
    COMMIT;
    dbms_output.put_line( 'ADDM-Tasks gelöscht: ' || v_deleted );
END;
/

Die Optimizer Statistics History (WRI$_OPTSTAT_*) wächst bei aktiver Statistikerfassung kontinuierlich. DBMS_STATS.PURGE_STATS löscht alle Statistik-Historieneinträge vor dem angegebenen Zeitstempel.

-- Konfigurierte Stats-History-Retention (Default: 31 Tage)
SELECT
    dbms_stats.get_stats_history_retention AS retention_days
FROM
    dual;
 
-- Ältester noch verfügbarer Statistik-Eintrag
SELECT
    dbms_stats.get_stats_history_availability AS oldest_available
FROM
    dual;

-- Alle Statistik-History-Einträge löschen die älter als 31 Tage sind
-- (entspricht dem Default-Retention-Wert)
BEGIN
    dbms_stats.purge_stats(
        before_timestamp =>
            systimestamp - INTERVAL '31' DAY
    );
    COMMIT;
END;
/

-- Aggressivere Bereinigung: nur die letzten 14 Tage behalten
BEGIN
    dbms_stats.purge_stats(
        before_timestamp =>
            systimestamp - INTERVAL '14' DAY
    );
    COMMIT;
END;
/

Nach dem Löschen der Daten ist der High-Watermark der Segmente noch nicht zurückgesetzt — der Platz ist logisch frei, aber physisch noch belegt. Ein Shrink gibt den Platz an den Tablespace zurück.

-- Offizielle AWR-Move-Prozedur (verschiebt AWR temporär und zurück
-- um den Platz freizugeben) — nur in Absprache mit Oracle Support ausführen!
-- Die MOVE_PROCEDURE je Komponente aus V$SYSAUX_OCCUPANTS entnehmen:
SELECT
    occupant_name,
    move_procedure
FROM
    v$sysaux_occupants
WHERE
    move_procedure IS NOT NULL
ORDER BY
    occupant_name;

-- Kleinste mögliche Größe einer SYSAUX-Datendatei ermitteln
SELECT
    file_name,
    round(bytes / 1024 / 1024, 2)     AS current_mb,
    round(maxbytes / 1024 / 1024, 2)  AS max_mb,
    autoextensible
FROM
    dba_data_files
WHERE
    tablespace_name = 'SYSAUX'
ORDER BY
    file_id;

-- Freien Platz am Ende der Datei ermitteln (für RESIZE-Kandidaten)
-- Liefert den Block ab dem die Datei gekürzt werden kann
SELECT
    CEIL(( nvl(hwm, 1) * blksize ) / 1024 / 1024) AS resize_to_mb,
    file_name
FROM
    (
        SELECT
            MAX(( block_id + blocks - 1 )) AS hwm,
            file_id
        FROM
            dba_extents
        WHERE
            tablespace_name = 'SYSAUX'
        GROUP BY
            file_id
    ) e
    JOIN dba_data_files f ON e.file_id = f.file_id
    CROSS JOIN (
        SELECT VALUE AS blksize
        FROM   v$parameter
        WHERE  name = 'db_block_size'
    )
ORDER BY
    file_name;

-- SYSAUX Datafile verkleinern (Zielgröße aus obiger Abfrage + Puffer)
-- Beispiel: Resize auf 8 GB — Wert aus dem resize_to_mb-Query ableiten!
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/sysaux01.dbf'
    RESIZE 8192M;

-- Vergleich vor/nach — immer beide Werte im Ticket festhalten
SELECT
    occupant_name,
    round(space_usage_kbytes / 1024, 2) AS space_mb_nach_bereinigung
FROM
    v$sysaux_occupants
WHERE
    occupant_name IN (
        'SM/AWR',
        'SM/ADVISOR',
        'SM/OPTSTAT',
        'SM/OTHER'
    )
ORDER BY
    space_usage_kbytes DESC;

Die Bereinigung aus Abschnitt 3 schafft einmalig Luft im SYSAUX. Ohne Konfigurationsanpassungen ist der Tablespace in wenigen Wochen wieder voll. Dieser Abschnitt beschreibt die dauerhaften Stellhebel.

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ist der einzige unterstützte Weg — direkte Tabellenmanipulation in WRM$_* ist nicht supported.

-- Aktuelle Konfiguration vor der Änderung dokumentieren
SELECT
    snap_interval,
    retention,
    topnsql
FROM
    dba_hist_wr_control;

-- AWR-Konfiguration setzen
-- retention     : in Minuten  (43200 = 30 Tage | 50400 = 35 Tage)
-- interval      : in Minuten  (60 = 1 Stunde, empfohlen für Produktion)
-- topnsql       : Anzahl Top-SQL-Statements je Snapshot (Default 30)
BEGIN
    dbms_workload_repository.modify_snapshot_settings(
        retention => 43200,   -- 30 Tage in Minuten
        INTERVAL  => 60,      -- 60 Minuten Snapshot-Intervall
        topnsql   => 30       -- nur Top-30-SQL je Snapshot speichern
    );
    COMMIT;
END;
/

-- Aktuellen Platz pro Snapshot-Tag messen
-- Gibt Anhaltspunkt für Hochrechnung bei geänderter Retention
SELECT
    trunc(s.begin_interval_time, 'DD')  AS snap_day,
    COUNT(*)                            AS snapshots,
    round(
        SUM(
            ( SELECT space_usage_kbytes
              FROM   v$sysaux_occupants
              WHERE  occupant_name = 'SM/AWR' )
            / ( SELECT COUNT(*)
                FROM   dba_hist_snapshot
                WHERE  dbid = ( SELECT dbid FROM v$database ) )
        ) / 1024, 2
    )                                   AS estimated_awr_mb_per_day
FROM
    dba_hist_snapshot s
WHERE
    s.dbid = ( SELECT dbid FROM v$database )
GROUP BY
    trunc(s.begin_interval_time, 'DD')
ORDER BY
    snap_day DESC
FETCH FIRST 7 ROWS ONLY;

Baselines verlängern die Retention ihrer Snapshots automatisch. Alte oder vergessene Baselines können massiv Platz belegen:

-- Alle AWR-Baselines prüfen
SELECT
    baseline_name,
    baseline_type,
    start_snap_id,
    end_snap_id,
    expiration,
    created,
    last_modified
FROM
    dba_hist_baseline
ORDER BY
    created;

-- Nicht mehr benötigte Baseline löschen
BEGIN
    dbms_workload_repository.drop_baseline(
        baseline_name => 'BASELINE_NAME_HIER',
        cascade       => TRUE   -- löscht auch die zugehörigen Snapshots
    );
    COMMIT;
END;
/

-- Aktuelle Retention prüfen (Default: 31 Tage)
SELECT
    dbms_stats.get_stats_history_retention AS current_retention_days
FROM
    dual;

-- Retention auf 15 Tage setzen
-- Reicht für RESTORE_TABLE_STATS bei Statistik-Regressionen aus
BEGIN
    dbms_stats.alter_stats_history_retention( retention => 15 );
END;
/

Oracle führt per Default drei automatische Maintenance-Tasks aus:

• auto optimizer stats collection — Statistik-Sammlung
• auto space advisor — Segment Advisor
• sql tuning advisor — Automatic SQL Tuning

-- Überblick: welche AutoTask-Jobs sind aktiv?
SELECT
    client_name,
    STATUS,
    consumer_group,
    window_group,
    logging_level,
    last_good_date
FROM
    dba_autotask_client
ORDER BY
    client_name;

-- Laufzeit-History der AutoTask-Jobs der letzten 30 Tage
SELECT
    client_name,
    window_name,
    window_start_time,
    window_duration,
    job_status,
    job_start_time,
    job_duration
FROM
    dba_autotask_client_history
WHERE
    window_start_time >= sysdate - 30
ORDER BY
    window_start_time DESC,
    client_name;

-- Einzelnen AutoTask-Job deaktivieren
BEGIN
    dbms_auto_task_admin.disable(
        client_name => 'sql tuning advisor',  -- Exakter Name aus DBA_AUTOTASK_CLIENT
        operation   => NULL,
        window_name => NULL
    );
END;
/
 
-- Wieder aktivieren
BEGIN
    dbms_auto_task_admin.enable(
        client_name => 'sql tuning advisor',
        operation   => NULL,
        window_name => NULL
    );
END;
/

-- Gültige client_name-Werte (Kopiervorlage)
-- 'auto optimizer stats collection'
-- 'auto space advisor'
-- 'sql tuning advisor'

Ein erhöhter Logging-Level (FULL) verdoppelt das Datenvolumen der Advisor-Task-Ergebnisse in den WRI$_ADVISOR_*-Tabellen:

-- Logging-Level prüfen
SELECT
    client_name,
    logging_level
FROM
    dba_autotask_client;

-- Logging auf BASIC setzen (reduziert WRI$_ADVISOR_*-Wachstum)
BEGIN
    dbms_auto_task_admin.set_client_prop(
        client_name   => 'sql tuning advisor',
        prop_name     => 'LOGGING LEVEL',
        prop_value    => 'BASIC'
    );
    dbms_auto_task_admin.set_client_prop(
        client_name   => 'auto space advisor',
        prop_name     => 'LOGGING LEVEL',
        prop_value    => 'BASIC'
    );
    COMMIT;
END;
/

Die AutoTask-Jobs laufen innerhalb definierter Maintenance Windows. Zu breite Fenster bedeuten längere Laufzeiten und mehr Advisor-Output.

-- Alle Maintenance Windows mit Zeiten und Ressourcen-Plan
SELECT
    window_name,
    enabled,
    active,
    resource_plan,
    duration,
    next_start_date,
    repeat_interval
FROM
    dba_scheduler_windows
WHERE
    window_name LIKE '%WINDOW%'
ORDER BY
    next_start_date;

-- Welche Jobs sind welchem Window zugeordnet?
SELECT
    window_name,
    client_name
FROM
    dba_autotask_window_clients
ORDER BY
    window_name,
    client_name;

-- Wochentag-Nachtfenster auf 2 Stunden begrenzen
-- Damit wird verhindert dass Advisor-Jobs die gesamte Nacht laufen
BEGIN
    dbms_scheduler.set_attribute(
        name      => 'MONDAY_WINDOW',
        attribute => 'DURATION',
        VALUE     => INTERVAL '2' HOUR
    );
    dbms_scheduler.set_attribute(
        name      => 'TUESDAY_WINDOW',
        attribute => 'DURATION',
        VALUE     => INTERVAL '2' HOUR
    );
    dbms_scheduler.set_attribute(
        name      => 'WEDNESDAY_WINDOW',
        attribute => 'DURATION',
        VALUE     => INTERVAL '2' HOUR
    );
    dbms_scheduler.set_attribute(
        name      => 'THURSDAY_WINDOW',
        attribute => 'DURATION',
        VALUE     => INTERVAL '2' HOUR
    );
    dbms_scheduler.set_attribute(
        name      => 'FRIDAY_WINDOW',
        attribute => 'DURATION',
        VALUE     => INTERVAL '2' HOUR
    );
    -- Wochenende mehr Zeit lassen (weniger Last)
    dbms_scheduler.set_attribute(
        name      => 'SATURDAY_WINDOW',
        attribute => 'DURATION',
        VALUE     => INTERVAL '4' HOUR
    );
    dbms_scheduler.set_attribute(
        name      => 'SUNDAY_WINDOW',
        attribute => 'DURATION',
        VALUE     => INTERVAL '4' HOUR
    );
    COMMIT;
END;
/

Einmalige Bereinigung reicht nicht — ein wöchentlicher Job verhindert die erneute Akkumulation ohne manuellen Eingriff:

-- ============================================================
-- Job: Wöchentliche Advisor-Task-Bereinigung
-- Löscht COMPLETED/ERROR-Tasks die älter als 30 Tage sind
-- Autor  : G. Pipperr
-- Version: 1.0
-- ============================================================
BEGIN
    dbms_scheduler.create_job(
        job_name        => 'JOB_ADVISOR_TASK_CLEANUP',
        job_type        => 'PLSQL_BLOCK',
        job_action      => q'[
DECLARE
    v_days_to_keep  NUMBER := 30;
    v_deleted       NUMBER := 0;
BEGIN
    FOR r IN (
        SELECT task_name
        FROM   dba_advisor_tasks
        WHERE  status  IN ( 'COMPLETED', 'ERROR', 'INITIAL' )
               AND status  != 'EXECUTING'
               AND created  < sysdate - v_days_to_keep
    ) LOOP
        BEGIN
            dbms_advisor.delete_task( task_name => r.task_name );
            v_deleted := v_deleted + 1;
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;
    COMMIT;
    -- Optimizer Stats History mitbereinigen
    dbms_stats.purge_stats(
        before_timestamp => systimestamp - interval '31' day
    );
    COMMIT;
END;
        ]',
        start_date      => trunc(sysdate + 1) + 2/24,  -- Morgen 02:00 Uhr
        repeat_interval => 'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=2;BYMINUTE=0',
        enabled         => TRUE,
        auto_drop       => FALSE,
        comments        => 'Wöchentliche Bereinigung Advisor-Tasks und Stats-History'
    );
    COMMIT;
END;
/

-- Job-Status prüfen
SELECT
    job_name,
    enabled,
    state,
    last_start_date,
    last_run_duration,
    next_run_date,
    run_count,
    failure_count
FROM
    dba_scheduler_jobs
WHERE
    job_name = 'JOB_ADVISOR_TASK_CLEANUP';

-- Aktuellen Wert prüfen
SELECT
    name,
    VALUE,
    description
FROM
    v$parameter
WHERE
    name = 'statistics_level';

-- Hochrechnung: Erwarteter SYSAUX-Bedarf nach Konfigurationsänderung
-- Grundlage: aktueller Verbrauch geteilt durch aktuelle Retention
-- multipliziert mit neuer Ziel-Retention
WITH awr_current AS (
    SELECT
        round(space_usage_kbytes / 1024, 2) AS current_awr_mb
    FROM
        v$sysaux_occupants
    WHERE
        occupant_name = 'SM/AWR'
),
retention_current AS (
    SELECT
        round(
            CAST(MAX(end_interval_time) AS DATE)
            - CAST(MIN(begin_interval_time) AS DATE), 1
        ) AS current_days
    FROM
        dba_hist_snapshot
    WHERE
        dbid = ( SELECT dbid FROM v$database )
)
SELECT
    a.current_awr_mb,
    r.current_days                                        AS current_retention_days,
    round(a.current_awr_mb / r.current_days, 2)          AS mb_per_day,
    round(( a.current_awr_mb / r.current_days ) * 30, 2) AS projected_30d_mb,
    round(( a.current_awr_mb / r.current_days ) * 35, 2) AS projected_35d_mb,
    round(( a.current_awr_mb / r.current_days ) * 60, 2) AS projected_60d_mb
FROM
    awr_current a
    CROSS JOIN retention_current r;

Nach der initialen Bereinigung und Konfiguration folgende Punkte abarbeiten:

1. [ ] AWR-Retention via DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS gesetzt
2. [ ] Optimizer-Stats-Retention auf 14–21 Tage gesetzt
3. [ ] AutoTask Logging-Level auf BASIC gesetzt
4. [ ] Maintenance-Window-Dauer begrenzt (Wochentags ≤ 3 Stunden)
5. [ ] Alter AWR-Baselines geprüft und bereinigt
6. [ ] SPM Plan-Retention von 53 auf 26 Wochen reduziert (DBMS_SPM.CONFIGURE)
7. [ ] SMB-Platzlimit geprüft — SPACE_BUDGET_PERCENT in DBA_SQL_MANAGEMENT_CONFIG
8. [ ] Unbenutzte SQL Plan Baselines bereinigt (nicht-akzeptierte Pläne > 180 Tage)
9. [ ] EXECUTION_DAYS_TO_EXPIRE für AUTO_STATS_ADVISOR_TASK geprüft — nicht UNLIMITED und Größe WRI$_ADV_OBJECTS geprüft!
10. [ ] prvt_advisor.delete_expired_tasks ausgeführt
11. [ ] Scheduler-Job für wöchentliche Advisor-Task-Bereinigung eingerichtet und auch für PDB geprüft!
12. [ ] V$SYSAUX_OCCUPANTS-Report als Baseline dokumentiert
13. [ ] SYSAUX-Wachstum in Monitoring (OEM/Nagios) als Alert hinterlegt
14. [ ] Alle Änderungen im Change-Ticket dokumentiert

-- Monitoring-Query: SYSAUX-Wachstum täglich prüfen
-- Geeignet als Basis für einen OEM-Metric-Extension oder Nagios-Check
SELECT
    occupant_name,
    round(space_usage_kbytes / 1024, 2)   AS space_mb,
    round(space_usage_kbytes / 1024 / 1024, 3) AS space_gb,
    CASE
        WHEN space_usage_kbytes / 1024 > 10240 THEN 'KRITISCH  > 10 GB'
        WHEN space_usage_kbytes / 1024 >  5120 THEN 'WARNUNG   > 5 GB'
        ELSE 'OK'
    END                                   AS STATUS
FROM
    v$sysaux_occupants
WHERE
    space_usage_kbytes > 102400   -- nur Komponenten > 100 MB anzeigen
ORDER BY
    space_usage_kbytes DESC;

Da der SYSAUX Tablespace zu fragmentierung neigt und dann nicht so einfach neu aufgebaut werden kann, sollte z.b. das Audit auf jeden Fall verschoben werden (siehe dazu den Tablespace Abschnitt unter Oracle Unified Auditing – Pflicht in 23ai - Umstellung von Klassischen/Mixed Audit auf Unified Auditing in der 19c als Vorbereitung für die 23ai Migration ).

Was kann aber alles verschoben werden?

Die View V$SYSAUX_OCCUPANTS enthält neben dem Platzbedarf auch die Spalte MOVE_PROCEDURE. Diese gibt an ob und wie eine Komponente aus dem SYSAUX in einen anderen Tablespace verschoben werden kann.

-- Vollständige Übersicht aller Spalten der View
SELECT
    occupant_name,
    occupant_desc,
    schema_name,
    round(space_usage_kbytes / 1024, 2)  AS space_mb,
    move_procedure,
    move_procedure_desc
FROM
    v$sysaux_occupants
ORDER BY
    space_usage_kbytes DESC;

SM/AWR, SM/ADVISOR, SM/OPTSTAT und SM/OTHER haben keine MOVE_PROCEDURE. Diese Komponenten sind fest im SYSAUX verankert und können nicht in einen anderen Tablespace ausgelagert werden. Der einzige Hebel ist die Bereinigung und Konfiguration der Retention — ein Umzug ist keine Option.

-- Verschiebbare Komponenten mit aktuellem Platzbedarf
-- Basis für die Entscheidung was zuerst ausgelagert wird
SELECT
    occupant_name,
    occupant_desc,
    schema_name,
    round(space_usage_kbytes / 1024, 2)  AS space_mb,
    move_procedure
FROM
    v$sysaux_occupants
WHERE
    move_procedure IS NOT NULL
ORDER BY
    space_usage_kbytes DESC;

Beispielausgabe (Oracle AI Database 26ai Enterprise Edition Release 23.26.1.2.0):

OCCUPANT_NAME        SPACE_MB  MOVE_PROCEDURE
-------------------- --------  ----------------------------------------
SDO                     76.50  MDSYS.MOVE_SDO
AUDSYS                  10.69  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
TEXT                     8.56  DRI_MOVE_CTXSYS
XDB                      0.94  XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE
SQL_FIREWALL/LOGS        0.88  DBMS_SQL_FIREWALL.MOVE_LOG_TABLES
XSOQHIST                 0.00  DBMS_XSOQ.OlapiMoveProc
WM                       0.00  DBMS_WM.move_proc
ORDIM                    0.00  ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA            0.00  ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS         0.00  ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA 0.00  ordsys.ord_admin.move_ordim_tblspc
EM                       0.00  emd_maintenance.move_em_tblspc
ULTRASEARCH              0.00  MOVE_WK
AO                       0.00  DBMS_AW.MOVE_AWMETA
XSAMD                    0.00  DBMS_AMD.Move_OLAP_Catalog
AUDIT_TABLES             0.00  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
ULTRASEARCH_DEMO_USER    0.00  MOVE_WK
LOGMNR                   0.00  SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY                 0.00  SYS.DBMS_LOGSTDBY.SET_TABLESPACE

19 rows selected.

Diese Auslagerungen schaffen echte Luft im SYSAUX — weil diese Komponenten anders als AWR und Advisors einen eigenen Tablespace bekommen können.

Vor dem Umzug einen dedizierten Tablespace anlegen:

-- Ziel-Tablespace für Audit-Daten anlegen
CREATE TABLESPACE tbs_audit
    DATAFILE '/u01/app/oracle/oradata/ORCL/audit01.dbf'
    SIZE 2048M
    AUTOEXTEND ON NEXT 512M MAXSIZE 20480M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    SEGMENT SPACE MANAGEMENT AUTO;

-- Unified Audit Trail in den neuen Tablespace verschieben
BEGIN
    dbms_audit_mgmt.set_audit_trail_location(
        audit_trail_type           => dbms_audit_mgmt.audit_trail_unified,
        audit_trail_location_value => 'TBS_AUDIT'
    );
END;
/

-- Ergebnis prüfen: AUDSYS sollte nun auf TBS_AUDIT zeigen
SELECT
    occupant_name,
    schema_name,
    round(space_usage_kbytes / 1024, 2) AS space_mb
FROM
    v$sysaux_occupants
WHERE
    occupant_name = 'AUDSYS';

Relevant wenn LogMiner aktiv genutzt wird oder Logical Standby konfiguriert ist:

-- LogMiner-Dictionary in eigenen Tablespace verschieben
-- Tablespace muss vorher existieren
EXECUTE SYS.DBMS_LOGMNR_D.SET_TABLESPACE('TBS_LOGMNR');

Relevant bei intensiver Oracle Text / Hybrid Vector Search Nutzung:

-- Oracle Text Komponenten in eigenen Tablespace verschieben
-- Ausführen als SYS oder CTXSYS
EXECUTE CTXSYS.DRI_MOVE_CTXSYS('TBS_TEXT');

Empfohlene Reihenfolge wenn SYSAUX entlastet werden soll:

1. AUDSYS (Unified Audit) — oft 1–10 GB, einfach zu verschieben, sofortige Wirkung
2. LOGMNR — relevant bei aktiver LogMiner/Streams-Nutzung
3. TEXT — relevant bei großem Oracle Text / Vector Search Volumen
4. SDO — relevant bei intensiver Spatial-Nutzung

Danach die nicht-verschiebbaren Komponenten (AWR, Advisors) über Retention und Bereinigung in den Griff bekommen — wie in den Abschnitten 3 und 4 beschrieben.

-- Vor jedem Move: Restriktive Mode-Prüfung
-- Manche Moves benötigen restricted session oder exclusive locks
SELECT
    logins,
    database_status,
    active_state
FROM
    v$instance;

-- Nach einem Move: Prüfen ob der Platzbedarf im SYSAUX zurückgegangen ist
-- (kann einige Minuten dauern bis V$SYSAUX_OCCUPANTS aktualisiert ist)
SELECT
    occupant_name,
    round(space_usage_kbytes / 1024, 2) AS space_mb_nach_move
FROM
    v$sysaux_occupants
ORDER BY
    space_usage_kbytes DESC;

Quellen:

• 26ai: Reference — V$SYSAUX_OCCUPANTS
• 26ai: Administrator's Guide — Managing the SYSAUX Tablespace

Ab Oracle 21c ist die Multitenant-Architektur mit CDB und PDBs verpflichtend — Non-CDB Datenbanken existieren nicht mehr. Alle in diesem Artikel beschriebenen Maßnahmen gelten grundsätzlich unverändert, müssen aber in einer CDB auf beiden Ebenen durchgeführt werden:

• Im CDB$ROOT — für die Advisor-Daten der Container-Ebene
• In jeder PDB — da jede PDB ihren eigenen SYSAUX-Tablespace hat

Das folgende Query liefert eine CDB-weite Übersicht über alle SYSAUX-Belegungen — als SYSTEM (nicht als SYS!) ausführen:

-- Als SYSTEM ausführen (nicht SYS!)
-- Zeigt SYSAUX-Belegung für CDB$ROOT und alle PDBs inkl. PDB$SEED
ALTER SESSION SET "_exclude_seed_cdb_view" = FALSE;
 
SELECT
    c.con_id,
    c.name                                        AS container_name,
    o.occupant_name,
    o.occupant_desc,
    round(o.space_usage_kbytes / 1024, 2)         AS space_mb
FROM
    containers ( v$sysaux_occupants ) o
    JOIN v$containers c ON c.con_id = o.con_id
WHERE
    o.space_usage_kbytes > 0
ORDER BY
    c.con_id,
    o.space_usage_kbytes DESC;

-- Alternativ: Nur die Top-Verbraucher über alle Container
ALTER SESSION SET "_exclude_seed_cdb_view" = FALSE;
 
SELECT
    c.con_id,
    c.name                                        AS container_name,
    o.occupant_name,
    round(o.space_usage_kbytes / 1024, 2)         AS space_mb
FROM
    containers ( v$sysaux_occupants ) o
    JOIN v$containers c ON c.con_id = o.con_id
WHERE
    o.space_usage_kbytes > 102400   -- nur Komponenten > 100 MB
ORDER BY
    o.space_usage_kbytes DESC,
    c.con_id;

-- Gesamtverbrauch SYSAUX je Container (Summe aller Komponenten)
ALTER SESSION SET "_exclude_seed_cdb_view" = FALSE;
 
SELECT
    c.con_id,
    c.name                                         AS container_name,
    round(SUM(o.space_usage_kbytes) / 1024, 2)    AS total_sysaux_mb
FROM
    containers ( v$sysaux_occupants ) o
    JOIN v$containers c ON c.con_id = o.con_id
GROUP BY
    c.con_id,
    c.name
ORDER BY
    total_sysaux_mb DESC;

Ab Oracle Database 19c können AWR-Snapshots sowohl auf CDB-Ebene als auch auf PDB-Ebene erzeugt werden. Standardmäßig sind automatische Snapshots für eine CDB aktiv, in 26ai auch für PDBs.

In einer CDB mit vielen PDBs kann unkontrollierter PDB-Level AWR den Speicher explodieren lassen — jede PDB schreibt stündlich Snapshots in ihren eigenen SYSAUX.

-- PDB-Autoflush Status prüfen (als CDB-Admin im CDB$ROOT)
SELECT
    con_id,
    name,
    VALUE
FROM
    v$system_parameter
WHERE
    name = 'awr_pdb_autoflush_enabled'
ORDER BY
    con_id;

-- PDB-Autoflush global für alle PDBs deaktivieren (CDB$ROOT)
-- Sinnvoll wenn AWR-Auswertung nur auf CDB-Ebene erfolgt
ALTER SYSTEM SET awr_pdb_autoflush_enabled = FALSE
    SCOPE = BOTH;

-- PDB-Autoflush nur für eine bestimmte PDB deaktivieren
-- Verbindung direkt zur Ziel-PDB herstellen, dann:
ALTER SYSTEM SET awr_pdb_autoflush_enabled = FALSE
    SCOPE = BOTH;

Alle Bereinigungsschritte aus Abschnitt 3 und alle Konfigurationsschritte aus Abschnitt 4 müssen in jeder PDB einzeln ausgeführt werden. Ein praktischer Ansatz ist ein CDB-weites Skript:

-- Template: AWR-Retention in allen PDBs auf 30 Tage setzen
-- Als CDB-Admin im CDB$ROOT ausführen
DECLARE
    v_retention_min  NUMBER := 43200;  -- 30 Tage in Minuten
    v_interval_min   NUMBER := 60;     -- 60 Minuten Snapshot-Intervall
BEGIN
    FOR r IN (
        SELECT con_id, name
        FROM   v$containers
        WHERE  con_id  > 1              -- PDB$SEED und CDB$ROOT ausschließen
               AND name != 'PDB$SEED'
               AND open_mode = 'READ WRITE'
    ) LOOP
        dbms_output.put_line(
            'Konfiguriere AWR in PDB: ' || r.name
            || ' (CON_ID=' || r.con_id || ')'
        );
        EXECUTE IMMEDIATE
            'ALTER SESSION SET CONTAINER = ' || dbms_assert.enquote_name( r.name );
 
        dbms_workload_repository.modify_snapshot_settings(
            retention => v_retention_min,
            INTERVAL  => v_interval_min,
            topnsql   => 30
        );
 
        EXECUTE IMMEDIATE 'ALTER SESSION SET CONTAINER = CDB$ROOT';
    END LOOP;
    dbms_output.put_line( 'AWR-Konfiguration in allen PDBs abgeschlossen.' );
END;
/

-- Template: Optimizer-Stats-Retention in allen PDBs auf 14 Tage setzen
DECLARE
BEGIN
    FOR r IN (
        SELECT con_id, name
        FROM   v$containers
        WHERE  con_id  > 1
               AND name != 'PDB$SEED'
               AND open_mode = 'READ WRITE'
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER SESSION SET CONTAINER = ' || dbms_assert.enquote_name( r.name );
 
        dbms_stats.alter_stats_history_retention( retention => 14 );
 
        EXECUTE IMMEDIATE 'ALTER SESSION SET CONTAINER = CDB$ROOT';
    END LOOP;
    dbms_output.put_line( 'Stats-Retention in allen PDBs gesetzt.' );
END;
/

-- Template: AutoTask SQL Tuning Advisor in allen PDBs auf BASIC-Logging setzen
DECLARE
BEGIN
    FOR r IN (
        SELECT con_id, name
        FROM   v$containers
        WHERE  con_id  > 1
               AND name != 'PDB$SEED'
               AND open_mode = 'READ WRITE'
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER SESSION SET CONTAINER = ' || dbms_assert.enquote_name( r.name );
 
        dbms_auto_task_admin.set_client_prop(
            client_name => 'sql tuning advisor',
            prop_name   => 'LOGGING LEVEL',
            prop_value  => 'BASIC'
        );
 
        EXECUTE IMMEDIATE 'ALTER SESSION SET CONTAINER = CDB$ROOT';
    END LOOP;
    dbms_output.put_line( 'AutoTask Logging-Level in allen PDBs gesetzt.' );
END;
/

Zusätzlich zur allgemeinen Checkliste aus Abschnitt 4 für CDB-Umgebungen:

1. [ ] CDB-weites SYSAUX-Analyse-Query ausgeführt (CONTAINERS-View, als SYSTEM)
2. [ ] AWR_PDB_AUTOFLUSH_ENABLED je PDB geprüft und ggf. deaktiviert
3. [ ] AWR-Retention in CDB$ROOT und allen PDBs gesetzt
4. [ ] Optimizer-Stats-Retention in CDB$ROOT und allen PDBs gesetzt
5. [ ] AutoTask-Konfiguration in CDB$ROOT und allen PDBs gesetzt
6. [ ] Scheduler-Bereinigungsjob in CDB$ROOT und allen relevanten PDBs eingerichtet
7. [ ] PDB$SEED geprüft — oft werden dort default-Konfigurationen vererbt

-- PDB$SEED kurz öffnen und Konfiguration setzen
ALTER PLUGGABLE DATABASE pdb$seed OPEN READ WRITE;
 
ALTER SESSION SET CONTAINER = pdb$seed;
 
-- AWR-Retention im Seed setzen (wird von neuen PDBs geerbt)
BEGIN
    dbms_workload_repository.modify_snapshot_settings(
        retention => 43200,  -- 30 Tage
        INTERVAL  => 60
    );
    dbms_stats.alter_stats_history_retention( retention => 14 );
END;
/
 
ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER PLUGGABLE DATABASE pdb$seed OPEN READ ONLY;

Quellen

• 26ai: Managing AWR in a Multitenant Environment
• Markus Flechtner: Mein SYSAUX-Tablespace wächst und wächst (inkl. CDB-Analyse-Skript)

Übersicht & Einstieg

• Oracle AI Database 26ai — Get Started with Performance Tuning (PDF)
• Oracle AI Database 26ai — Database Performance Tuning Guide (PDF, vollständig)

AWR (Automatic Workload Repository)

• 26ai: Automatic Performance Diagnostics — AWR & ADDM

ADDM (Automatic Database Diagnostic Monitor)

• 26ai: Automatic Database Diagnostic Monitor (ADDM) — Overview & Konfiguration
• 26ai: AutoTask — Viewing Automatic SQL Tuning Results (inkl. ADDM-Trigger)

SQL Tuning Advisor (STA) / Automatic SQL Tuning

• 26ai: SQL Tuning Guide — Analyzing SQL with SQL Tuning Advisor
• 26ai: Tuning SQL Manually Using SQL Tuning Advisor
• 26ai: Viewing Automatic SQL Tuning Results (AutoTask)

SQL Access Advisor

• 26ai: SQL Tuning Guide — SQL Access Advisor (Abschnitt Optimizing Data Access Paths)

Optimizer Statistics Advisor

• 26ai: Analyzing Statistics Using Optimizer Statistics Advisor

Segment Advisor

• 26ai: Administrator's Guide — Managing Space for Schema Objects (Segment Advisor)

SQL Performance Analyzer (SPA)

• 26ai: Real Application Testing — Introduction to SQL Performance Analyzer

DBMS_ADVISOR — PL/SQL Package Referenz (alle Advisors)

• 26ai: PL/SQL Packages and Types Reference — DBMS_ADVISOR

Externer Überblick (Oracle Coretec Blog, Juni 2024)

• Oracle Database Advisors — Overview (alle Advisors tabellarisch, mit Links)

• Talip Hakan Özturk — Step by Step Documentation
• https://www.markusdba.de/2025/06/22/mein-sysaux-tablespace-waechst-und-waechst-was-soll-kann-ich-tun/