Check

SELECT client_name,

       status 

  FROM dba_autotask_client

 WHERE client_name = 'auto space advisor';

History

SET LINESIZE 200

SELECT window_name,

       jobs_created,

       jobs_started,

       jobs_completed

  FROM dba_autotask_client_history

 WHERE client_name='auto space advisor'

   AND window_start_time >= SYSDATE -7

 ORDER BY window_start_time DESC;

Run this to see the status of the Auto Space Advisor job over the last 7 days...

SET LINESIZE 200 

SET PAGESIZE 100

COLUMN job_name FORMAT a30

COLUMN window_name FORMAT a30

SELECT window_name,

       job_name,

       job_start_time,

       job_status,

       job_duration

  FROM dba_autotask_job_history

 WHERE client_name='auto space advisor'

   AND job_start_time >= SYSDATE -7

 ORDER BY job_start_time DESC;

Tasks

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

SET LINESIZE 200 

SET PAGESIZE 100

COLUMN status FORMAT a30

SELECT task_name,

       advisor_name,

       execution_start,

       execution_end,

       status,

       recommendation_count

  FROM dba_advisor_tasks

 WHERE execution_end > sysdate-1

   AND description = 'Auto Space Advisor';

Only tasks with recommendations...

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

SET LINESIZE 200 

SET PAGESIZE 100

COLUMN status FORMAT a30

SELECT task_name,

       advisor_name,

       execution_start,

       execution_end,

       status,

       recommendation_count

  FROM dba_advisor_tasks

 WHERE description = 'Auto Space Advisor'

   AND recommendation_count > 0

 ORDER BY execution_start;

To check job status...

COLUMN owner FORMAT a10

COLUMN comments FORMAT a60

SET LINESIZE 132

SELECT owner,

       job_name,

       enabled,

       comments

  FROM dba_scheduler_jobs

 WHERE job_name = 'AUTO_SPACE_ADVISOR_JOB';

To check duration...

SELECT *

  FROM (SELECT log_date,

               job_name,

               status,

               actual_start_date,

               run_duration

          FROM dba_scheduler_job_run_details

         WHERE job_name='AUTO_SPACE_ADVISOR_JOB'

         ORDER BY log_id DESC)

WHERE rownum<=2;

To check schedule...

COLUMN schedule_name FORMAT a30

SELECT job_name,

       schedule_type,

       schedule_name

  FROM dba_scheduler_jobs

 WHERE job_name = 'AUTO_SPACE_ADVISOR_JOB';

SELECT * 

  FROM dba_scheduler_wingroup_members;

SELECT window_name,

       repeat_interval

  FROM dba_scheduler_windows;

COLUMN last_start_date FORMAT a40

COLUMN window_name FORMAT a17

COLUMN next_start_date FORMAT a40

COLUMN duration FORMAT a15

SET LINESIZE 132

SELECT window_name,

       next_start_date,

       last_start_date,

       duration

  FROM dba_scheduler_windows;

Enable

BEGIN

  DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto space advisor',

                              operation   => NULL,

                              window_name => NULL);

END;

/

Disable

BEGIN

  DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',

                               operation   => NULL,

                               window_name => NULL);

END;

/

Or, alternatively, at DBMS Scheduler level (recommended for 10GR2 and earlier only)...

EXECUTE DBMS_SCHEDULER.DISABLE('AUTO_SPACE_ADVISOR_JOB');

Run Segment Advisor Manually

Viewing Results

To see findings...

SELECT dao.type AS object_type,

       dao.attr1 AS owner,

       dao.attr2 AS object_name,

       dao.attr4 AS tablespace,

       daf.message,

       daf.more_info

  FROM dba_advisor_findings daf,

       dba_advisor_objects dao

 WHERE daf.task_name = 'SYS_AUTO_SPCADV_38130608082021'

   AND daf.task_id = dao.task_id

   AND daf.object_id = dao.object_id;

To see findings with actions...

SELECT dao.type AS object_type,

       dao.attr1 AS owner,

       dao.attr2 AS object_name,

       dao.attr4 AS tablespace,

       daf.message,

       daf.more_info,

       daa.command,

       daa.attr1,

       daa.attr2,

       daa.attr3,

       daa.attr4

  FROM dba_advisor_findings daf,

       dba_advisor_objects dao,

       dba_advisor_actions daa

 WHERE daf.task_name = 'SYS_AUTO_SPCADV_38130608082021'

   AND daf.task_id = dao.task_id

   AND daf.object_id = dao.object_id

   AND dao.task_id = daa.task_id

   AND dao.object_id = daa.object_id;

Troubleshooting

Bibliography