Wednesday, November 16, 2011

Oracle OEM Advisory Findings using query

       Oracle Advisory Findings

SELECT   TO_CHAR (AT.execution_end, ' hh24:mi:ss') "DATE",
         af.MESSAGE finding, aa.MESSAGE action
    FROM dba_advisor_tasks AT,
         dba_advisor_findings af,
         dba_advisor_recommendations ar,
         dba_advisor_actions aa
   WHERE AT.owner = af.owner
     AND AT.task_id = af.task_id
     AND af.owner = ar.owner(+)
     AND af.task_id = ar.task_id(+)
     AND af.finding_id = ar.finding_id(+)
     AND ar.owner = aa.owner(+)
     AND ar.task_id = aa.task_id(+)
     AND ar.rec_id = aa.rec_id(+)
     AND AT.execution_end > SYSDATE - 1 / 24
ORDER BY AT.execution_end DESC, af.finding_id, ar.rec_id;

      Oracle SEGMENT Advisor Recommendation:

column recommendations format a130 wrap
column c3 format a80 wrap heading 'Run Me Frist'
column c2 format a80 wrap heading 'Run Me Second'
column c1 format a80 wrap heading 'Run Me Last |(May not be required)'
set lines 132
set pages 66
set echo off
set feedback off
set verify off
ttitle left _date center 'Report of Table Rebuild Candidates' skip 2
select segment_owner, segment_name, segment_type, partition_name,
          recommendations, c3
          || ';' c3, c2
          || ';' c2, c1
          || ';' c1
from table (DBMS_SPACE.asa_recommendations ('FALSE', 'FALSE', 'FALSE'))
WHERE segment_type = 'TABLE'
-- Comment out these last two lines if you want all schemas/tables.
--and segment_owner = UPPER ('&&ENTER_TABLE_OWNER')
--and segment_name = UPPER ('&&ENTER_TABLE_NAME')

No comments: