Oracle Autotask - auto space advisor

Info:
The auto space advisor Calculates stats regarding space usage.
The auto space advisor Auto Task in Oracle can be very CPU and I/O intensive.
In large systems this task can run forever, generating load on the system

Better option would be to disable this task, and run the task upon demand

- Check Current Status:

COL CLIENT_NAME for A40

COL STATUS for A20
SELECT client_name, status FROM DBA_AUTOTASK_CLIENT;

CLIENT_NAME                         STATUS
----------------------------------- --------------------------------
auto optimizer stats collection     DISABLED
auto space advisor                  ENABLED
sql tuning advisor                  DISABLED

- To Enable/Disable Auto Task for Gathering Statistics:

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto space advisor', 
    operation => NULL, 
    window_name => NULL);
END;
/

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto space advisor', 
    operation => NULL, 
    window_name => NULL);
END;
/

See Auto Tasks status

SELECT client_name, status, window_group  

  FROM DBA_AUTOTASK_CLIENT;

client_name             status window_group

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

sql tuning advisor   ENABLED ORA$AT_WGRP_SQ

auto optimizer stats collection ENABLED ORA$AT_WGRP_OS

auto space advisor   ENABLED ORA$AT_WGRP_SA

See Auto Tasks history. In case the job running the task was dropped, task would still be running, but giving an error "ORA-27367: program "SYS"."AUTO_SPACE_ADVISOR_PROG" associated with this job is disabled"

SELECT * 

  FROM DBA_AUTOTASK_JOB_HISTORY 

 WHERE client_name = 'auto space advisor' 

   AND job_start_time > SYSDATE -30

 ORDER BY job_start_time DESC;

 

 

CLIENT_NAME         WINDOW_NAME      WINDOW_START_TIME   WINDOW_DURATION  JOB_NAME              JOB_STATUS  JOB_START_TIME      JOB_DURATION JOB_ERROR JOB_INFO        

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

auto space advisor SUNDAY_WINDOW     20260208 06:00:00   0 20:0:0.184801  ORA$AT_SA_SPC_SY_37362 FAILED     20260208 22:05:06   0 0:0:0.0    27367     ORA-27367: program "SYS"."AUTO_SPACE_ADVISOR_PROG" associated with this job is disabled

auto space advisor SUNDAY_WINDOW     20260208 06:00:00   0 20:0:0.184801  ORA$AT_SA_SPC_SY_37359 FAILED     20260208 18:04:54   0 0:0:0.0    27367     ORA-27367: program "SYS"."AUTO_SPACE_ADVISOR_PROG" associated with this job is disabled

auto space advisor SUNDAY_WINDOW     20260208 06:00:00   0 20:0:0.184801  ORA$AT_SA_SPC_SY_37356 FAILED     20260208 14:04:44   0 0:0:0.0    27367     ORA-27367: program "SYS"."AUTO_SPACE_ADVISOR_PROG" associated with this job is disabled

auto space advisor SUNDAY_WINDOW     20260208 06:00:00   0 20:0:0.184801  ORA$AT_SA_SPC_SY_37353 FAILED     20260208 10:04:29   0 0:0:0.0    27367     ORA-27367: program "SYS"."AUTO_SPACE_ADVISOR_PROG" associated with this job is disabled

auto space advisor SUNDAY_WINDOW     20260208 06:00:00   0 20:0:0.184801  ORA$AT_SA_SPC_SY_37333 FAILED     20260208 06:00:03   0 0:0:0.0    27367     ORA-27367: program "SYS"."AUTO_SPACE_ADVISOR_PROG" associated with this job is disabled

auto space advisor SATURDAY_WINDOW   20260207 06:00:00   0 20:0:0.352690  ORA$AT_SA_SPC_SY_37322 FAILED     20260207 22:03:44   0 0:0:0.0    27367     ORA-27367: program "SYS"."AUTO_SPACE_ADVISOR_PROG" associated with this job is disabled

auto space advisor SATURDAY_WINDOW   20260207 06:00:00   0 20:0:0.352690  ORA$AT_SA_SPC_SY_37319 FAILED     20260207 18:03:25   0 0:0:0.0    27367     ORA-27367: program "SYS"."AUTO_SPACE_ADVISOR_PROG" associated with this job is disabled

auto space advisor SATURDAY_WINDOW   20260207 06:00:00   0 20:0:0.352690  ORA$AT_SA_SPC_SY_37316 FAILED     20260207 14:03:14   0 0:0:0.0    27367     ORA-27367: program "SYS"."AUTO_SPACE_ADVISOR_PROG" associated with this job is disabled

The solution would be to disable the task, and run it manually

Disable the task:

BEGIN

  DBMS_AUTO_TASK_ADMIN.DISABLE(

  client_name => 'auto space advisor',

  operation => NULL,

  window_name => NULL);

END;

/

Run task manually:

BEGIN

  DBMS_SPACE.auto_space_advisor_job_proc;

END;

/