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;
/

Finally print the list….for doing a better analysis in detail.

-- 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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s