DECLARE
v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');
v_min_id NUMBER;
v_max_id NUMBER;
v_dbid NUMBER;
BEGIN
SELECT MIN(snap_id), MAX(snap_id), MIN(dbid)
INTO v_min_id, v_max_id, v_dbid
FROM dba_hist_snapshot
WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE);
DBMS_ADDM.analyze_db(
task_name => v_task_name,
begin_snapshot => v_min_id,
end_snapshot => v_max_id,
db_id => v_dbid
);
END;
SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
BEGIN
DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd'));
END;
spool testfile.txt
DECLARE
v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');
v_min_id NUMBER;
v_max_id NUMBER;
v_dbid NUMBER;
BEGIN
SELECT MIN(snap_id), MAX(snap_id), MIN(dbid)
INTO v_min_id, v_max_id, v_dbid
FROM dba_hist_snapshot
WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE);
DBMS_ADDM.analyze_db(
task_name => v_task_name,
begin_snapshot => v_min_id,
end_snapshot => v_max_id,
db_id => v_dbid
);
END;
/
SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
BEGIN
DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd'));
END;
/
SQL> DECLARE
2 v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');
3 v_min_id NUMBER;
4 v_max_id NUMBER;
5 v_dbid NUMBER;
6 BEGIN
7 SELECT MIN(snap_id), MAX(snap_id), MIN(dbid)
8 INTO v_min_id, v_max_id, v_dbid
9 FROM dba_hist_snapshot
10 WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE);
11
12 DBMS_ADDM.analyze_db(
13 task_name => v_task_name,
14 begin_snapshot => v_min_id,
15 end_snapshot => v_max_id,
16 db_id => v_dbid
17 );
18 END;
19 /
PL/SQL procedure successfully completed.
SQL> SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
DBMS_ADDM.GET_REPORT(USER||'_'||TO_CHAR(SYSDATE-1,'YYYY-MM-DD'))
--------------------------------------------------------------------------------
ADDM Report for Task 'SYSTEM_2015-06-07'
-------------------
SQL> BEGIN
2 DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd'));
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> exit
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_addm.htm#ARPLS65059