Run an Oracle ADDM report over the last 24 hour period; only intended to be run on an instance, not a RAC service; Bommarito Consulting, LLC: http://bommaritollc.com/

run_addm.sql

SET pagesize 0;
SET heading off echo off feedback off verify off;

VARIABLE task_name VARCHAR2(40);

DECLARE
  start_snap_id NUMBER := 0;
  end_snap_id NUMBER := 0;
  job_name VARCHAR2(255);
  job_buff CLOB;
BEGIN
  -- Enable
  DBMS_OUTPUT.ENABLE(20000);

  -- Get the end snap_id
  SELECT snap_id INTO end_snap_id
        FROM (SELECT MAX(SNAP_ID) AS snap_id
          FROM DBA_HIST_SNAPSHOT);

  -- Get the end snap_id
  SELECT snap_id INTO start_snap_id
        FROM (SELECT MAX(SNAP_ID) AS snap_id
                FROM DBA_HIST_SNAPSHOT
                WHERE EXTRACT(HOUR FROM (systimestamp - end_interval_time)) >= 24);

  -- Set the job name
  SELECT 'ADDM_' || TO_CHAR(start_snap_id) || '_' || TO_CHAR(end_snap_id)
    INTO job_name
    FROM DUAL;

  :task_name := job_name;

  -- Create an ADDM task.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'ADDM',
    task_name         => job_name,
    task_desc         => 'Automated ADDM job');

  -- Set the start and end snapshots.
  DBMS_ADVISOR.set_task_parameter (
    task_name => job_name,
    parameter => 'START_SNAPSHOT',
    value     => start_snap_id);

  DBMS_ADVISOR.set_task_parameter (
    task_name => job_name,
    parameter => 'END_SNAPSHOT',
    value     => end_snap_id);

  -- Execute the task.
  DBMS_ADVISOR.execute_task(task_name => job_name);

  -- Uncommenting these lines outputs the job results to stdout
  --job_buff :=
  --DBMS_OUTPUT.PUT_LINE(job_buff);
END;
/

SPOOL addm.txt
SET long 1000000 pagesize 0 longchunksize 1000
COLUMN get_clob format a80

SELECT DBMS_ADVISOR.get_task_report(:task_name)
  FROM dual;

QUIT;