środa, 29 stycznia 2014

Segment advisor - query


SELECT o.type AS object_type,
  o.attr1     AS schema,
  O.ATTR2     AS OBJECT_NAME,
  F.MESSAGE,
  ROUND(TO_NUMBER(SUBSTR(F.MORE_INFO,INSTR(F.MORE_INFO,':',1,1)+1,INSTR(F.MORE_INFO,':',1,2)-INSTR(F.MORE_INFO,':',1,1)-1))/(1024*1024)) ALLOCATED_SPACE,
  ROUND(TO_NUMBER(SUBSTR(F.MORE_INFO,INSTR(F.MORE_INFO,':',1,3)+1,INSTR(F.MORE_INFO,':',1,4)-INSTR(F.MORE_INFO,':',1,3)-1))/(1024*1024)) USED_SPACE,
  round(to_number(SUBSTR(f.more_info,INSTR(f.more_info,':',1,5)+1,INSTR(f.more_info,':',1,6)-INSTR(f.more_info,':',1,5)-1))/(1024*1024)) reclaim_space
FROM dba_advisor_findings f
JOIN DBA_ADVISOR_OBJECTS O
ON F.OBJECT_ID  = O.OBJECT_ID
AND F.TASK_NAME = O.TASK_NAME
where F.MESSAGE like '%shrink%'
ORDER BY 7 desc;

Brak komentarzy:

Prześlij komentarz