Got this from one of the sites.. which is tested in 12c
V$RECOVERY_AREA_USAGE has only aggregated information about space reclaimable without the details about which files are reclaimable or not. This query shows the details.
Note that in 11g you have to remove the 'FOREIGN ARCHIVED LOG' part in the UNION ALL.
V$RECOVERY_AREA_USAGE has only aggregated information about space reclaimable without the details about which files are reclaimable or not. This query shows the details.
Note that in 11g you have to remove the 'FOREIGN ARCHIVED LOG' part in the UNION ALL.
set linesize 200 pagesize 1000
column file_name format a100
SELECT file_type,
name file_name,
space_used bytes,
CASE
WHEN space_reclaimable>=space_used
THEN 'YES'
ELSE 'NO'
END reclaimable,
completion_time
FROM
(SELECT 'CONTROL FILE' file_type,
name,
CAST(NULL AS DATE) completion_time,
(
CASE
WHEN ceilasm = 1
AND name LIKE '+%'
THEN ceil(((block_size*file_size_blks)+1)/1048576)*1048576
ELSE block_size *file_size_blks
END) space_used,
0 space_reclaimable,
1 number_of_files
FROM v$controlfile,
(SELECT /*+ no_merge */
ceilasm FROM x$krasga
)
WHERE is_recovery_dest_file = 'YES'
UNION ALL
SELECT 'REDO LOG' file_type,
member,
CAST(NULL AS DATE),
(
CASE
WHEN ceilasm = 1
AND member LIKE '+%'
THEN ceil((l.bytes+1)/1048576)*1048576
ELSE l.bytes
END) space_used,
0 space_reclaimable,
1 number_of_files
FROM
(SELECT group#, bytes FROM v$log
UNION
SELECT group#, bytes FROM v$standby_log
) l,
v$logfile lf,
(SELECT /*+ no_merge */
ceilasm FROM x$krasga
)
WHERE l.group# = lf.group#
AND lf.is_recovery_dest_file = 'YES'
UNION ALL
SELECT 'ARCHIVED LOG' file_type,
name,
completion_time,
(al.file_size) space_used,
(
CASE
WHEN dl.rectype = 11
THEN al.file_size
ELSE 0
END) space_reclaimable,
1 number_of_files
FROM
(SELECT recid,
name,
completion_time,
CASE
WHEN ceilasm = 1
AND name LIKE '+%'
THEN ceil(((blocks*block_size)+1)/1048576)*1048576
ELSE blocks * block_size
END file_size
FROM v$archived_log,
(SELECT /*+ no_merge */
ceilasm FROM x$krasga
)
WHERE is_recovery_dest_file = 'YES'
AND name IS NOT NULL
) al,
x$kccagf dl
WHERE al.recid = dl.recid(+)
AND dl.rectype(+) = 11
UNION ALL
SELECT 'BACKUP PIECE' file_type,
handle,
completion_time,
(bp.file_size) space_used,
(
CASE
WHEN dl.rectype = 13
THEN bp.file_size
ELSE 0
END) space_reclaimable,
1 number_of_files
FROM
(SELECT recid,
handle,
completion_time,
CASE
WHEN ceilasm = 1
AND handle LIKE '+%'
THEN ceil((bytes+1)/1048576)*1048576
ELSE bytes
END file_size
FROM v$backup_piece,
(SELECT /*+ no_merge */
ceilasm FROM x$krasga
)
WHERE is_recovery_dest_file = 'YES'
AND handle IS NOT NULL
) bp,
x$kccagf dl
WHERE bp.recid = dl.recid(+)
AND dl.rectype(+) = 13
UNION ALL
SELECT 'IMAGE COPY' file_type,
name,
completion_time,
(dc.file_size) space_used,
(
CASE
WHEN dl.rectype = 16
THEN dc.file_size
ELSE 0
END) space_reclaimable,
1 number_of_files
FROM
(SELECT recid,
name,
completion_time,
CASE
WHEN ceilasm = 1
AND name LIKE '+%'
THEN ceil(((blocks*block_size)+1)/1048576)*1048576
ELSE blocks * block_size
END file_size
FROM v$datafile_copy,
(SELECT /*+ no_merge */
ceilasm FROM x$krasga
)
WHERE is_recovery_dest_file = 'YES'
AND name IS NOT NULL
) dc,
x$kccagf dl
WHERE dc.recid = dl.recid(+)
AND dl.rectype(+) = 16
UNION ALL
SELECT 'FLASHBACK LOG' file_type,
name,
first_time,
NVL(fl.space_used, 0) space_used,
NVL(fb.reclsiz, 0) space_reclaimable,
NVL(fl.number_of_files, 0) number_of_files
FROM
(SELECT name,
first_time,
(
CASE
WHEN ceilasm = 1
AND name LIKE '+%'
THEN ceil((fl.bytes+1)/1048576)*1048576
ELSE bytes
END)space_used,
1 number_of_files
FROM v$flashback_database_logfile fl,
(SELECT /*+ no_merge */
ceilasm FROM x$krasga
)
) fl,
(SELECT SUM(to_number(fblogreclsiz)) reclsiz FROM x$krfblog
)fb
UNION ALL
SELECT 'FOREIGN ARCHIVED LOG' file_type,
rlnam,
CAST(NULL AS DATE),
(rlr.file_size) space_used,
(
CASE
WHEN rlr.purgable = 1
THEN rlr.file_size
ELSE 0
END) space_reclaimable,
1 number_of_files
FROM
(SELECT rlnam,
CASE
WHEN ceilasm = 1
AND rlnam LIKE '+%'
THEN ceil(((rlbct*rlbsz)+1)/1048576)*1048576
ELSE rlbct *rlbsz
END file_size,
CASE
WHEN bitand(rlfl2, 4096) = 4096
THEN 1
WHEN bitand(rlfl2, 8192) = 8192
THEN 1
ELSE 0
END purgable
FROM x$kccrl,
(SELECT /*+ no_merge */
ceilasm FROM x$krasga
)
WHERE bitand(rlfl2, 64) = 64
AND rlnam IS NOT NULL
)rlr
UNION ALL
SELECT 'AUXILIARY DATAFILE COPY' file_type,
adfcnam,
CAST(NULL AS DATE),
(adc.file_size) space_used,
(
CASE
WHEN adc.purgable = 1
THEN adc.file_size
ELSE 0
END) space_reclaimable,
1 number_of_files
FROM
(SELECT adfcnam,
CASE
WHEN ceilasm = 1
AND adfcnam LIKE '+%'
THEN ceil(((adfcnblks*adfcbsz)+1)/1048576)*1048576
ELSE adfcnblks *adfcbsz
END file_size,
adfcrecl purgable
FROM x$kccadfc,
(SELECT /*+ no_merge */
ceilasm FROM x$krasga
)
WHERE bitand(adfcflg, 1) = 1
AND adfcnam IS NOT NULL
)adc
)fusg
ORDER BY completion_time nulls last;
Goto top
Sample output
FILE_TYPE FILE_NAME BYTES RECLAIMABLE COMPLETIO
------------ ------------------------------------------------------------------- ----------- ---------
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_9_9v5cn1kv_.arc 81408 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_7_9v59wnsb_.arc 599552 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_4_9v57fdtn_.arc 10725376 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_8_9v5cd035_.arc 112640 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_11_9v5ffd57_.arc 2515968 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_12_9v5fqd0k_.arc 112640 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_13_9v5fz8z1_.arc 529408 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_14_9v5hgkqr_.arc 94208 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_5_9v598zsz_.arc 2663424 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_10_9v5cxtr4_.arc 312832 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_6_9v59lvv7_.arc 127488 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_17_9v5jcds8_.arc 126464 NO 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_16_9v5j2968_.arc 634368 NO 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_15_9v5hrgtv_.arc 2430976 NO 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_18_9v5kny3b_.arc 190976 NO 01-JUL-14
BACKUP PIECE /fra/demo/backupset/2014_07_18/o1_mf_annnn_TAG20140718T... 17882624 NO 18-JUL-14
CONTROL FILE /fra/demo/controlfile/o1_mf_9v506z2f_.ctl 10027008 NO
No comments:
Post a Comment