RE: looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP
- From: Herring Dave - dherri <Dave.Herring@xxxxxxxxxx>
- To: "Christopher.Taylor2@xxxxxxxxxxxx" <Christopher.Taylor2@xxxxxxxxxxxx>, "dbaprimatics@xxxxxxxxx" <dbaprimatics@xxxxxxxxx>
- Date: Fri, 5 Oct 2012 18:40:27 +0000
Chris,
Does all processing have to reside within the database or are OS scripts
possible? If so, then you could use the following:
Put this block of code in a script:
--------------------------------------------------------------
SET FEEDBACK off HEADING off LINESIZE 1500
VARIABLE DBID NUMBER;
BEGIN
SELECT dbid INTO :DBID FROM v$database;
END;
/
SET ECHO on
DEFINE INSTANCE_ID=&1;
DEFINE BEGIN_DATETIME='&2';
DEFINE END_DATETIME='&3';
SET ECHO off
SELECT *
FROM TABLE(dbms_workload_repository.awr_report_html(
(:DBID), &INSTANCE_ID,
(SELECT MIN(LEAST(snap_id))
FROM dba_hist_snapshot
WHERE end_interval_time >=
TO_DATE('&BEGIN_DATETIME', 'DD-MON-YY HH24:MI:SS')
AND dbid = :DBID),
(SELECT MAX(GREATEST(snap_id))
FROM dba_hist_snapshot
WHERE end_interval_time <=
TO_DATE('&END_DATETIME', 'DD-MON-YY HH24:MI:SS')
AND dbid = :DBID), 8));
EXIT
----------------------------------------------
Execute the script using:
echo "connect <user>/<pass>
@<script from above> $inst $begindt $enddt
exit" | sqlplus -s /nolog | uuencode awr_daily_rpt.html | mailx -s "Chris's
daily AWR report" <chris's email addr>
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring@xxxxxxxxxx
TEL 630.944.4762
MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: Thursday, October 04, 2012 9:04 PM
To: dbaprimatics@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP
Right, but I'm curious about doing it in the database :) I've got it working
well minus the few seconds it takes to generate the clob and attach it.
Chris
From: Vamshi Damidi [mailto:dbaprimatics@xxxxxxxxx]
Sent: Thursday, October 04, 2012 7:27 PM
To: Taylor Christopher - Nashville
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP
Hi Chris,
Probably shell script should work fine along with mutt for attachments as email
client since awr report can be spooled to html file.
Thanks,
Vamshi .D
On Thu, Oct 4, 2012 at 4:04 PM,
<Christopher.Taylor2@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>>
wrote:
I figured out how to send attachments using UTL_SMTP (from code found on the
web) and the code I'm using uses a clob variable to build the attachment.
So I pieced together a function to build a clob from
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML and call that function from my mail
procedure.
This works surprisingly well to email me a daily AWR report (for a specific
window of time - nightly batch cycle) *yet* the performance is slow to build
the clob.
I had looked for a way to read the AWR_REPORT_HTML results directly into an
attachment but I couldn't figure it out (being very non-proficient in pl/sql)
so I was wondering if there was a better way to do this? (To capture the
results of AWR_REPORT_HTML and mail them as an attachment).
I was thinking I should probably use a bulk collect here perhaps? Or am I
overthinking this?
Here's my clob function (just for proof of concept):
CREATE OR REPLACE FUNCTION html_clob
RETURN CLOB
IS
p_attach_clob CLOB := NULL;
CURSOR c1
IS
SELECT
output
FROM
TABLE (sys.DBMS_WORKLOAD_REPOSITORY.awr_report_html (99999999,
1,
20003,
20011)); BEGIN
FOR rec IN c1
LOOP
p_attach_clob := p_attach_clob ||chr(13)||chr(10)|| rec.output;
END LOOP;
RETURN p_attach_clob;
END;
/
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.
If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.
Thank You.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
