Archive Log Information

-- 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 );