#2383

Create procedure :

CREATE OR REPLACE PROCEDURE GENERATE_AWR_REPORT_YESTERDAY is

    v_dbid v$database.dbid%type;
    v_inst_num v$instance.instance_number%type;
    v_end_snapid dba_hist_snapshot.snap_id%type;
    v_begin_snapid dba_hist_snapshot.snap_id%type;
    v_start_date     VARCHAR2 (20);
    v_awr_report CLOB:=empty_clob();
    v_html_report_row varchar2(1500);
    po_err_msg varchar2(1000);
    v_date varchar2(8);
    v_db_name varchar2(50);

    fhandle  utl_file.file_type;

begin
    
    select d.dbid, i.instance_number, d.name into v_dbid, v_inst_num, v_db_name from v$database d, v$instance i;
    
    select to_char(sysdate-1, 'YYYYMMDD') into v_date from dual;
    
    select min(snap_id) MIN_SNAP_ID, max(snap_id) MAX_SNAP_ID 
    into v_begin_snapid, v_end_snapid
    from dba_hist_snapshot
    where trunc(begin_interval_time) = trunc(sysdate-1)
    and dbid = v_dbid and instance_number = v_inst_num;

    fhandle := utl_file.fopen(
                'AWR_REPORT_DIR'     -- File location
              , v_date||'_'||v_db_name||'_awrreport.html' -- File name
              , 'w' -- Open mode: w = write. 
                  );

    for cv in (select output from table(dbms_workload_repository.awr_report_html(v_dbid,v_inst_num, v_begin_snapid, v_end_snapid))) loop
        utl_file.put_line(fhandle, cv.output);
    end loop;
    
    utl_file.fclose(fhandle);
    
exception
  when others then
    dbms_output.put_line('ERROR: ' || SQLCODE || ' - ' || SQLERRM);
    raise;
end;

Create job

exec dbms_scheduler.create_job(
job_name=>'DAILY_AWR_JOB'
,job_type=>'STORED_PROCEDURE'
,job_action=>'GENERATE_AWR_REPORT_YESTERDAY'
,start_date=>to_date(to_char(sysdate,'DD Mon YYYY')||' 01:20:00','DD Mon YYYY HH24:MI:SS') 
,repeat_interval=>'FREQ=DAILY'
,auto_drop => false
,enabled => true)

AWR, database, DB, oracle, Perf

[Unix] Internet Speed CLI [Unix] RHEL Setup RDP server