select to_char(first_time,'YYYY-MON-DD') "Date", to_char(first_time,'DY') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23" , count(*) Total
from v$log_history group by to_char(first_time,'YYYY-MON-DD'), to_char(first_time,'DY')
order by to_date(to_char(first_time,'YYYY-MON-DD'),'YYYY-MON-DD')
/
Output:-
Date DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 TOTAL
----------- --- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
2016-AUG-21 SUN 8 0 36 45 27 8 52 0 9 0 0 0 8 4 0 0 8 0 0 0 10 0 6 0 221
2016-AUG-22 MON 8 0 51 46 53 7 0 0 8 0 0 0 8 0 0 0 8 0 4 0 11 0 6 0 210
2016-AUG-23 TUE 8 0 50 21 28 59 0 0 9 0 2 1 9 4 1 1 8 1 4 0 10 0 15 0 231
2016-AUG-24 WED 0 0 54 31 48 31 0 0 8 0 0 0 8 0 0 3 8 0 4 0 13 0 0 0 208
2016-AUG-25 THU 8 0 54 36 62 14 0 0 8 1 10 5 8 2 1 2 8 0 4 0 13 0 7 0 243
2016-AUG-26 FRI 8 0 55 50 45 18 0 0 9 0 0 0 8 6 0 1 21 5 4 0 13 0 7 0 250
2016-AUG-27 SAT 8 0 54 43 49 19 4 0 9 0 0 0 8 0 0 0 8 0 4 0 12 0 6 0 224
2016-AUG-28 SUN 8 0 49 39 12 29 31 0 9 0 0 0 8 0 0 0 8 0 0 0 10 0 6 0 209
2016-AUG-29 MON 8 0 53 44 38 38 0 4 9 0 0 1 8 0 0 0 8 0 4 0 10 0 6 0 231
2016-AUG-30 TUE 8 0 55 43 24 43 0 0 5 0 0 1 8 0 0 4 8 0 4 0 11 0 6 0 220
2016-AUG-31 WED 8 0 54 45 41 26 0 1 3 4 3 0 8 0 5 7 8 7 28 31 35 27 32 26 399
Full Backup Details
Select to_char(START_TIME, 'DD-MON-YY') time,INCREMENTAL_LEVEL "Level", round(sum(ORIGINAL_INPUT_BYTES)/1024/1024/1024,2) IN_GB,
round(sum(OUTPUT_BYTES)/1024/1024/1024,2) OUT_GB,to_char(min(START_TIME), 'DD-MON-YY HH12:MI:SS AM') Started,
to_char(max(COMPLETION_TIME),'DD-MON-YY HH12:MI:SS AM') Completed, round(sum(ELAPSED_SECONDS)/60/60,2) "TOTAL for all Channel(HRS)",
round((max(COMPLETION_TIME) - min(START_TIME))*24,2) "TOTAL (HRS) ",
trunc(((86400*((max(COMPLETION_TIME) - min(START_TIME))))/60)/60)-24*(trunc((((86400*((max(COMPLETION_TIME) - min(START_TIME))))/60)/60)/24)) "Hrs",
trunc((86400*((max(COMPLETION_TIME) - min(START_TIME))))/60)-60*(trunc(((86400*((max(COMPLETION_TIME) - min(START_TIME))))/60)/60)) "Min"
from V$BACKUP_SET_DETAILS
where INCREMENTAL_LEVEL=0
group by to_char(START_TIME, 'DD-MON-YY'),INCREMENTAL_LEVEL
order by to_date(to_char(START_TIME, 'DD-MON-YY'), 'DD-MON-YY');
Incremental Backup Details
SELECT TO_CHAR(START_TIME, 'DD-MON-YY') TIME,INCREMENTAL_LEVEL "LEVEL",
ROUND(SUM(ORIGINAL_INPUT_BYTES)/1024/1024/1024,2) IN_GB,
ROUND(SUM(OUTPUT_BYTES)/1024/1024/1024,2) OUT_GB,TO_CHAR(MIN(START_TIME), 'DD-MON-YY HH12:MI:SS AM') STARTED,
TO_CHAR(MAX(COMPLETION_TIME),'DD-MON-YY HH12:MI:SS AM') COMPLETED, ROUND(SUM(ELAPSED_SECONDS)/60/60,2) "TOTAL FOR ALL CHANNEL(HRS)",
ROUND((MAX(COMPLETION_TIME) - MIN(START_TIME))*24,2) "TOTAL (HRS) ",
TRUNC(((86400*((MAX(COMPLETION_TIME) - MIN(START_TIME))))/60)/60)-24*(TRUNC((((86400*((MAX(COMPLETION_TIME) - MIN(START_TIME))))/60)/60)/24)) "HRS",
TRUNC((86400*((MAX(COMPLETION_TIME) - MIN(START_TIME))))/60)-60*(TRUNC(((86400*((MAX(COMPLETION_TIME) - MIN(START_TIME))))/60)/60)) "MIN"
FROM V$BACKUP_SET_DETAILS
WHERE INCREMENTAL_LEVEL=1
GROUP BY TO_CHAR(START_TIME, 'DD-MON-YY'),INCREMENTAL_LEVEL
ORDER BY TO_DATE(TO_CHAR(START_TIME, 'DD-MON-YY'), 'DD-MON-YY');
|
|
Comments
Post a Comment