Oracle: To cleanup SYSAUX

 Safe SQL to Cleanup SYSAUX Advisor Data

SQL> SET SERVEROUTPUT ON

DECLARE

    CURSOR c_tasks IS

        SELECT task_name

        FROM   dba_advisor_log  -- Use user_advisor_log if you don't have DBA privileges

        WHERE  owner = USER;    -- Restrict to current schema

BEGIN

    FOR rec IN c_tasks LOOP

        BEGIN

            DBMS_ADVISOR.DELETE_TASK(rec.task_name);

            DBMS_OUTPUT.PUT_LINE('Deleted task: ' || rec.task_name);

        EXCEPTION

            WHEN OTHERS THEN

                DBMS_OUTPUT.PUT_LINE('Failed to delete task: ' || rec.task_name ||

                                     ' - ' || SQLERRM);

        END;

    END LOOP;

END;

/

-- Connect as SYS or a user with DBA privileges

BEGIN

    -- Purge all old advisor data

    DBMS_ADVISOR.DELETE_TASK(NULL);

    -- Optionally, purge all advisor logs

    DBMS_ADVISOR.DELETE_LOG(NULL);

    -- Optionally, purge all advisor findings

    DBMS_ADVISOR.DELETE_FINDING(NULL);

END;

/

---------------

Targeted Cleanup for Specific Advisor

BEGIN

    -- Purge SQL Tuning Advisor tasks older than 30 days

    DBMS_ADVISOR.DELETE_TASK(

        task_name => NULL,

        task_owner => NULL,

        task_type => 'SQL TUNING ADVISOR',

        created_before => SYSDATE - 30

    );

END;

/

------------

Check SYSAUX Usage Before and After

COLUMN occupant_name FORMAT A30

COLUMN space_usage_kbytes FORMAT 999,999,999

SELECT occupant_name, space_usage_kbytes

FROM v$sysaux_occupants

ORDER BY space_usage_kbytes DESC;