儲存過程批量生成awr指令碼

DECLARE  
  
  l_snap_start       NUMBER := 40078;  
  l_snap_end         NUMBER := 40081;  
  l_dir              VARCHAR2(50) := 'AWRTEST';  
    
  l_last_snap        NUMBER := NULL;  
  l_dbid             NUMBER := 2778659381;  
  l_file             UTL_FILE.file_type;  
  l_file_name        VARCHAR(50);  
  cursor cur_inum is  SELECT instance_number FROM dba_hist_snapshot WHERE dbid = l_dbid GROUP BY instance_number ORDER BY instance_number;  
  
  
BEGIN  
  
  for l_instance_number in cur_inum loop   
    l_last_snap := NULL;  
    FOR cur_snap IN (SELECT snap_id  
                   FROM   dba_hist_snapshot  
                   WHERE  instance_number = l_instance_number.instance_number  
                   AND    snap_id BETWEEN l_snap_start AND l_snap_end  
                   ORDER BY snap_id)  
  LOOP  
    IF l_last_snap IS NOT NULL THEN  
      l_file := UTL_FILE.fopen(l_dir, 'awr_' ||l_instance_number.instance_number||'_'|| l_last_snap || '_' || cur_snap.snap_id || '.html', 'w', 32767);  --40162
        
      FOR cur_rep IN (SELECT output  
                      FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number.instance_number, l_last_snap, cur_snap.snap_id)))  
      LOOP  
        UTL_FILE.put_line(l_file, cur_rep.output);  
      END LOOP;  
      UTL_FILE.fclose(l_file);  
    END IF;  
    l_last_snap := cur_snap.snap_id;  --40161
  END LOOP;  
  end loop;  
    
EXCEPTION  
  WHEN OTHERS THEN  
    IF UTL_FILE.is_open(l_file) THEN  
      UTL_FILE.fclose(l_file);  
    END IF;  
    RAISE;   
END;  

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12272958/viewspace-2150429/，如需轉載，請註明出處，否則將追究法律責任。