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

czwartek, 9 stycznia 2014

Monitoring RMAN backup

Query for EM 11 to monitoring all backups:

SELECT b.database_name                         AS "DB Name",
  t.type_qualifier1                            AS "Version",
  b.host                                       AS "Server Name" ,
  b.target_type                                AS "Type",
  TO_CHAR(b.start_time, 'YYYYMMDD-HH24:MI:SS') AS "Start Time",
  TO_CHAR(b.end_time, 'YYYYMMDD-HH24:MI:SS')   AS "End Time",
  b.input_type "Backup Info",
  b.time_taken_display   AS "Time taken",
  b.output_bytes_display AS "Final Size Bytes",
  status                 AS "Status"
FROM mgmt$ha_backup b,
  mgmt$target t
WHERE b.target_guid = t.target_guid
AND b.start_time    > sysdate -2
ORDER BY type_qualifier1,
  END_TIME ;


and progress restoring or backup from RMAN:

SELECT OPNAME,
  SOFAR                /TOTALWORK*100 PCT,
  TRUNC(TIME_REMAINING /60) MIN_RESTANTES,
  TRUNC(ELAPSED_SECONDS/60) MIN_ATEAGORA
FROM V$SESSION_LONGOPS
WHERE TOTALWORK>0
AND OPNAME LIKE '%RMAN%';