-- List archived files not backed-up
select NAME, ARCHIVED , deleted, status
from v$archived_log
where status='A';
-- Size of Archive Log file not backed up
select to_char(first_time,'dd/mm/yyyy') "FIRST_DATE", count(*),
sum(round(blocks*block_size/1024/1024)) "SIZE IN MB"
from v$archived_log
where status='A'
group by to_char(first_time,'dd/mm/yyyy')
order by 1 asc;
-- Check Archive Log File Size
SELECT TO_CHAR( TRUNC( first_time ), 'DD/MM/YYYY' ) day,COUNT(*) "Number of Archive Log Files", ROUND( SUM( blocks * block_size ) / 1024 / 1024 ) "SIZE IN MB"
FROM v$archived_log
WHERE first_time > ADD_MONTHS( sysdate, -1 )
GROUP BY TRUNC( first_time )
ORDER BY TRUNC( first_time );