Undo Advice
– This is a script which helps you to get the undo advise, when U badly need the same
– And do not have the OEM
Begin
Declare
currcount number :=0;
ndsqry char(300);
Begin
select nvl(count(*),0) into currcount from dba_tables where table_name = ‘SC_RESTAB2′;
if currcount = 0 then
dbms_output.put_line(‘Result table do not exists.. Creating the table’);
– Dynamically creating the result table
ndsqry := ‘create table sc_restab2 as ‘||
’SELECT af.task_name, af.impact, ao.type object_type, ao.attr1 schema,’||
’ao.attr2 AS object_name, af.message, af.more_info ‘||
’FROM dba_advisor_findings af, dba_advisor_objects ao ‘||
’where af.object_id = ao.object_id AND af.task_name = ao.task_name ‘||
’and 2=1′ ;
– dbms_output.put_line(ndsqry);
EXECUTE IMMEDIATE ndsqry;
else
dbms_output.put_line(‘Result table already exist …Deleteing rows from the same’);
ndsqry := ‘delete from sc_restab2′;
EXECUTE IMMEDIATE ndsqry;
commit;
end if;
Exception
when others then
raise_application_error(-20001,’An error was encountered *1*- ‘||SQLCODE||’ -ERROR- ‘||SQLERRM);
End;
DECLARE
l_task_name VARCHAR2(30);
l_object_id NUMBER;
l_start_snap_id NUMBER :=0;
l_end_snap_id NUMBER :=0;
curr_time varchar2(14);
ndsqry char(500);
BEGIN
– identify the snap id first
select min(snap_id),max(snap_id) into l_start_snap_id,l_end_snap_id from dba_hist_snapshot;
select to_char(sysdate,’DDMMYYYYHHMISS’) into curr_time from dual;
l_task_name := ’scund’||trim(to_char(l_start_snap_id))||’_'||trim(to_char(l_end_snap_id))||’_'||curr_time;
– dbms_output.put_line(l_task_name);
– l_task_name := ‘Test’;
– Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name => ‘Undo Advisor’,
task_name => l_task_name,
task_desc => ‘Undo Advisor Task’);
DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => ‘UNDO_TBS’,
attr1 => NULL,
attr2 => NULL,
attr3 => NULL,
attr4 => ‘null’,
attr5 => NULL,
object_id => l_object_id);
– Set the target object.
DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => ‘TARGET_OBJECTS’,
value => l_object_id);
– Set the start and end snapshots.
DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => ‘START_SNAPSHOT’,
value => l_start_snap_id);
DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => ‘END_SNAPSHOT’,
value => l_end_snap_id);
– Execute the task.
DBMS_ADVISOR.execute_task(task_name => l_task_name);
– Insert records in the result table
ndsqry:=’insert into sc_restab2 ‘||
’SELECT af.task_name, af.impact, ao.type object_type, ao.attr1 schema,’||
’ao.attr2 AS object_name, af.message, af.more_info ‘||
’FROM dba_advisor_findings af, dba_advisor_objects ao ‘||
’where af.object_id = ao.object_id AND af.task_name = ao.task_name ‘||
’and af.task_name = :a ‘;
EXECUTE IMMEDIATE ndsqry using l_task_name;
– dbms_output.put_line(ndsqry);
END;
End;
/
– Listing of advisor findings
Column task_name format A30
column impact format 999999999999
column object_type format A64
column schema format A30
column object_name format A64
column message format A100
column more_info format A100
set lines 450
set pages 1000
– Spool Output to undo_check
spool undo_check.lst
SELECT *
FROM sc_restab2
ORDER BY af.task_name, af.impact DESC
spool off
clear columns
