getarc_hh.sql 归档每小时

--getarc_hh.sql查归档的每小时的总数

col "Total" for 9999

col "h0~h23" for a100
SELECT  to_char(first_time,'yyyy-mm-dd') "Date",
        to_char(first_time, 'Dy') "Day",
        count(1) "Total",
        rpad(SUM(decode(to_char(first_time, 'hh24'),'00',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'01',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'02',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'03',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'04',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'05',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'06',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'07',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'08',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'09',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'10',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'11',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'12',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'13',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'14',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'15',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'16',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'17',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'18',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'19',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'20',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'21',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'22',1,0)),3,' ')||' '||
        rpad(SUM(decode(to_char(first_time, 'hh24'),'23',1,0)),3,' ') 
        "h0~h23"
FROM    V$log_history&dblink
group by to_char(first_time,'yyyy-mm-dd'), to_char(first_time, 'Dy')

Order by 1 desc;

效果如下：

--getarc.sql 查归档的总量和总数

SELECT trunc(SUM(BLOCKS *BLOCK_SIZE )/1024/1024/1024,2) AS "Size(G)",TRUNC(completion_time) archived_date FROM v$archived_log&&dblink GROUP BY TRUNC(completion_time) order by 2 desc;
select thread#,TRUNC(completion_time) archived_date,COUNT(*) from v$archived_log&&dblink GROUP BY thread#,TRUNC(completion_time) ORDER BY 2 DESC; 
undef dblink

效果如下：