00.30.828 PM 21-JAN-07 09.30.14.078 PM
7 21-JAN-07 08.30.41.296 PM 21-JAN-07 09.00.30.828 PM
6 21-JAN-07 08.00.56.093 PM 21-JAN-07 08.30.41.296 PM
Given these results, we will use a SNAP_ID range from 6 to 8 when we invoke Undo Advisor.
The PL/SQL anonymous block is as follows:
-- begin Undo Advisor analysis
-- rev. 1.1 RJB 7/16/2007
--
-- SQL*Plus variable to retrieve the task number from Segment Advisor
variable task_id number
declare
task_id number;
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name := ''; -- unique name generated from create_task
descr := 'Check Undo Tablespace';
dbms_advisor.create_task
('Undo Advisor', :task_id, name, descr);
dbms_advisor.create_object
(name, 'UNDO_TBS', NULL, NULL, NULL, 'null', obj_id);
dbms_advisor.set_task_parameter(name, 'TARGET_OBJECTS', obj_id);
dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', 6);
Chapter 6: Monitoring Space Usage 189
dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', 8);
dbms_advisor.set_task_parameter(name, 'INSTANCE', 1);
dbms_advisor.execute_task(name);
end;
PL/SQL procedure successfully completed.
SQL> print task_id
TASK_ID
-------
527
As with the Segment Advisor, we can review the DBA_ADVISOR_FINDINGS view to see the
problem and the recommendations.
SQL> select owner, task_id, task_name, type,
2 message, more_info from dba_advisor_findings
3 where task_id = 527;
OWNER TASK_ID TASK_NAME TYPE
---------- ------- ---------- -------------
RJB 527 TASK_00003 PROBLEM
MESSAGE
-----------------------------------------------------
The undo tablespace is OK.
Pages:
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344