-- ======================================================================
-- Actual and Total Space Per Hour
-- ======================================================================
select TRUNC(FIRST_TIME, 'HH') DAY,
ROUND((SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024), 2) GB,
ROUND((SUM(BACKUP_COUNT * BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024), 2) TOTAL_BACKUP_SIZE
FROM V$ARCHIVED_LOG
WHERE BACKUP_COUNT > 0
GROUP BY TRUNC(FIRST_TIME, 'HH')
ORDER BY day DESC;
-- ======================================================================
-- Check Additional Space by File
-- ======================================================================
WITH A AS (select VAL.SEQUENCE#,
VAL.BLOCKS,
VAL.FIRST_TIME,
VAL.NEXT_TIME,
VAL.BACKUP_COUNT,
VAL.COMPLETION_TIME,
VAL.DELETED,
(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_MB,
(VAL.BLOCKS * VAL.BLOCK_SIZE * VAL.BACKUP_COUNT) / 1024 / 1024 TOTAL_SIZE_MB
FROM V$ARCHIVED_LOG VAL
WHERE VAL.BACKUP_COUNT > 0
--AND VAL.FIRST_TIME BETWEEN TO_TIMESTAMP('2023/08/11 09:00', 'yyyy/mm/dd hh24:mi')
--AND TO_TIMESTAMP('2023/08/11 09:59', 'yyyy/mm/dd hh24:mi')
)
select A.SEQUENCE#,
A.FIRST_TIME,
A.COMPLETION_TIME,
A.BACKUP_COUNT,
A.DELETED,
A.SIZE_MB,
A.TOTAL_SIZE_MB,
ROUND((A.TOTAL_SIZE_MB - A.SIZE_MB), 2) EXTRA_MB_BACKED
FROM A
ORDER BY FIRST_TIME DESC;
-- ======================================================================
-- Additional Space Used Per Day
-- ======================================================================
WITH arch AS (select TRUNC(FIRST_TIME, 'DD') DAY,
ROUND((SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024), 2) GB,
ROUND((SUM(BACKUP_COUNT * BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024), 2) TOTAL_BACKUP_SIZE
FROM V$ARCHIVED_LOG
WHERE BACKUP_COUNT > 0
GROUP BY TRUNC(FIRST_TIME, 'DD')
ORDER BY 1 DESC)
select arch.day,
arch.GB,
arch.TOTAL_BACKUP_SIZE,
(arch.TOTAL_BACKUP_SIZE - arch.GB) ADDITONAL_GB_BACKED
FROM arch;
-- ======================================================================
-- Additional Space Used Per Month
-- ======================================================================
WITH arch AS (select TRUNC(COMPLETION_TIME, 'MM') DAY,
ROUND((SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024), 2) GB,
ROUND((SUM(BACKUP_COUNT * BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024), 2) TOTAL_BACKUP_SIZE
FROM V$ARCHIVED_LOG
WHERE BACKUP_COUNT > 0
GROUP BY TRUNC(COMPLETION_TIME, 'MM')
ORDER BY 1 DESC)
select arch.day,
arch.GB,
arch.TOTAL_BACKUP_SIZE,
(arch.TOTAL_BACKUP_SIZE - arch.GB) ADDITONAL_GB_BACKED
FROM arch;
