-- David Mann -- http://ba6.us -- 24-JUL-2012 -- Archived Log Heat Map for past 31 Days -- Requires access to v$archived_log -- Spool output to file and view with browser -- or use SQL Developer PL/SQL DBMS_OUTPUT report type -- I tried to use a scripted stylesheet but SQL Dev wouldn't cooperate so -- that is my excuse for all of the ugly inline CSS. For now :) --SET SERVEROUTPUT ON DECLARE myMax NUMBER; myDBName VARCHAR2(16); -- dec2hex Function from http://www.orafaq.com/wiki/Hexadecimal FUNCTION dec2hex (N in number) RETURN varchar2 IS hexval varchar2(64); N2 number := N; digit number; hexdigit char; BEGIN while ( N2 > 0 ) loop digit := mod(N2, 16); if digit > 9 then hexdigit := chr(ascii('A') + digit - 10); else hexdigit := to_char(digit); end if; hexval := hexdigit || hexval; N2 := trunc( N2 / 16 ); end loop; return hexval; END dec2hex; FUNCTION DataCell ( P_Value NUMBER, P_Max NUMBER) RETURN VARCHAR2 IS myReturn VARCHAR2(128); myColorVal NUMBER; myColorHex VARCHAR2(16); BEGIN myColorVal := ROUND( 255-FLOOR(255 * (P_VALUE / P_MAX))); myColorHex := LPAD(TRIM(dec2hex(myColorVal)) ,2,'0'); IF P_Value = P_Max THEN myColorHex := '00'; END IF; myReturn := '
Date / Hour | '); DBMS_OUTPUT.PUT_LINE('00 | '); DBMS_OUTPUT.PUT_LINE('01 | '); DBMS_OUTPUT.PUT_LINE('02 | '); DBMS_OUTPUT.PUT_LINE('03 | '); DBMS_OUTPUT.PUT_LINE('04 | '); DBMS_OUTPUT.PUT_LINE('05 | '); DBMS_OUTPUT.PUT_LINE('06 | '); DBMS_OUTPUT.PUT_LINE('07 | '); DBMS_OUTPUT.PUT_LINE('08 | '); DBMS_OUTPUT.PUT_LINE('09 | '); DBMS_OUTPUT.PUT_LINE('10 | '); DBMS_OUTPUT.PUT_LINE('11 | '); DBMS_OUTPUT.PUT_LINE('12 | '); DBMS_OUTPUT.PUT_LINE('13 | '); DBMS_OUTPUT.PUT_LINE('14 | '); DBMS_OUTPUT.PUT_LINE('15 | '); DBMS_OUTPUT.PUT_LINE('16 | '); DBMS_OUTPUT.PUT_LINE('17 | '); DBMS_OUTPUT.PUT_LINE('18 | '); DBMS_OUTPUT.PUT_LINE('19 | '); DBMS_OUTPUT.PUT_LINE('20 | '); DBMS_OUTPUT.PUT_LINE('21 | '); DBMS_OUTPUT.PUT_LINE('22 | '); DBMS_OUTPUT.PUT_LINE('23 | '); DBMS_OUTPUT.PUT_LINE('
'||TO_CHAR(cur.Day,'DD-MON-YYYY')||' | '); DBMS_OUTPUT.PUT_LINE( DataCell(cur."00", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."01", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."02", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."03", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."04", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."05", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."06", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."07", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."08", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."09", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."10", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."11", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."12", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."13", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."14", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."15", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."16", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."17", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."18", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."19", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."20", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."21", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."22", myMax) ); DBMS_OUTPUT.PUT_LINE( DataCell(cur."23", myMax) ); DBMS_OUTPUT.PUT_LINE('