archives

SET tab off underline off linesize 2050 newpage none pagesize 4000
SET feedback ON colsep | editfile /tmp/sqlplusedit.tmp LONG 10000000 trimout ON;
SET numwidth 13;

SET serveroutput ON size 1000000 trimspool ON longchunksize 10000000 TIME ON;
WITH
Q1 AS (
SELECT ROWNUM RN, q1a.LH_DATE
FROM (SELECT DISTINCT TRUNC (q1b.FIRST_TIME) LH_DATE
FROM V$LOG_HISTORY q1b
ORDER BY TRUNC (q1b.FIRST_TIME) DESC) q1a
WHERE ROWNUM <= 9
),
Q2 AS (
SELECT ROWNUM - 1 HN
FROM DBA_OBJECTS
WHERE ROWNUM <= 24
),
Q3 AS (
SELECT TRUNC (q3a.FIRST_TIME) LH_DATE, TO_CHAR (q3a.FIRST_TIME, 'HH24') LH_HN,
NVL (q3b.BLOCKS * q3b.BLOCK_SIZE + 1024, 2048) LH_SZ
FROM V$LOG_HISTORY q3a,
V$ARCHIVED_LOG q3b
WHERE q3a.THREAD# = q3b.THREAD#
AND q3a.SEQUENCE# = q3b.SEQUENCE#
)
SELECT LPAD (C1, MAX (LENGTH (C1)) over (), C0P) ||' = ['||
REPLACE (LPAD (C2, MAX (LENGTH (C2)) over (), C0P), ' 0M', '   ') ||'|'||
REPLACE (LPAD (C3, MAX (LENGTH (C3)) over (), C0P), ' 0M', '   ') ||'|'||
REPLACE (LPAD (C4, MAX (LENGTH (C4)) over (), C0P), ' 0M', '   ') ||'|'||
REPLACE (LPAD (C5, MAX (LENGTH (C5)) over (), C0P), ' 0M', '   ') ||'|'||
REPLACE (LPAD (C6, MAX (LENGTH (C6)) over (), C0P), ' 0M', '   ') ||'|'||
REPLACE (LPAD (C7, MAX (LENGTH (C7)) over (), C0P), ' 0M', '   ') ||'|'||
REPLACE (LPAD (C8, MAX (LENGTH (C8)) over (), C0P), ' 0M', '   ') ||'|'||
REPLACE (LPAD (C9, MAX (LENGTH (C9)) over (), C0P), ' 0M', '   ') ||'|'||
REPLACE (LPAD (C10, MAX (LENGTH (C10)) over (), C0P), ' 0M', '   ') ||']'
FROM (
(SELECT 0 C0O, ' ' C0P, 'HH' C1,
NVL (TO_CHAR (MAX (CASE WHEN a.RN = 9 THEN a.LH_DATE ELSE NULL END), ' DDMon '), '00Zzz') C2,
NVL (TO_CHAR (MAX (CASE WHEN a.RN = 8 THEN a.LH_DATE ELSE NULL END), ' DDMon '), '00Zzz') C3,
NVL (TO_CHAR (MAX (CASE WHEN a.RN = 7 THEN a.LH_DATE ELSE NULL END), ' DDMon '), '00Zzz') C4,
NVL (TO_CHAR (MAX (CASE WHEN a.RN = 6 THEN a.LH_DATE ELSE NULL END), ' DDMon '), '00Zzz') C5,
NVL (TO_CHAR (MAX (CASE WHEN a.RN = 5 THEN a.LH_DATE ELSE NULL END), ' DDMon '), '00Zzz') C6,
NVL (TO_CHAR (MAX (CASE WHEN a.RN = 4 THEN a.LH_DATE ELSE NULL END), ' DDMon '), '00Zzz') C7,
NVL (TO_CHAR (MAX (CASE WHEN a.RN = 3 THEN a.LH_DATE ELSE NULL END), ' DDMon '), '00Zzz') C8,
NVL (TO_CHAR (MAX (CASE WHEN a.RN = 2 THEN a.LH_DATE ELSE NULL END), ' DDMon '), '00Zzz') C9,
NVL (TO_CHAR (MAX (CASE WHEN a.RN = 1 THEN a.LH_DATE ELSE NULL END), ' DDMon '), '00Zzz') C10
FROM Q1 a
UNION ALL
SELECT 1 C0O, '-' C0P, '-' C1, '-' C2, '-' C3, '-' C4, '-' C5, '-' C6, '-' C7, '-' C8, '-' C9, '-' C10
FROM dual
UNION ALL
SELECT 26 C0O, '-' C0P, '-' C1, '-' C2, '-' C3, '-' C4, '-' C5, '-' C6, '-' C7, '-' C8, '-' C9, '-' C10
FROM dual
UNION ALL
SELECT 27 C0O, ' ' C0P, 'DD' C1,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 9 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C2,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 8 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C3,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 7 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C4,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 6 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C5,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 5 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C6,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 4 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C7,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 3 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C8,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 2 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C9,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 1 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C10
FROM Q1 q1,
Q3 q3
WHERE q3.LH_DATE = q1.LH_DATE)
UNION ALL
(SELECT 2 + q3.LH_HN C0O, ' ' C0P, TO_CHAR (q3.LH_HN, 'FM00') C1,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 9 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C2,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 8 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C3,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 7 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C4,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 6 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C5,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 5 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C6,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 4 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C7,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 3 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C8,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 2 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C9,
' '|| TO_CHAR (SUM (CASE WHEN q1.RN = 1 THEN q3.LH_SZ ELSE 0 END) / 1048576, 'FM999G999G990')||'M' C10
FROM Q1 q1,
Q2 q2,
Q3 q3
WHERE q3.LH_DATE = q1.LH_DATE
AND q3.LH_HN = TO_CHAR (q2.HN, 'FM00')
GROUP BY (q3.LH_HN))
)
ORDER BY C0O
/