view_fra.sql
set lines 185
col df format 999,999,999 heading “Datafile Size (in MB)”
col tf format 999,999,999 heading “Tempfile Size (in MB)”
col rd format 999,999,999 heading “Redo Logfile Size (in MB)”
col nm format a50 heading “Location”
col sl format 999,999,999 heading “Size (in MB)”
col su format 999,999,999 heading “Used (in MB)”
col sr format 999,999,999 heading “Reclaimable (in MB)”
col nf format 999,999,999 heading “Number of Files”
col pc format 999.99 heading “Percent Full”
prompt Database Size:
select sum(bytes/1024/1024) df from v$datafile;
select sum(bytes/1024/1024) tf from v$tempfile;
select sum(bytes/1024/1024) rd from v$log;
prompt “”
prompt “Flash Recovery Area Details:”
prompt “”
select substr(name,1,50) nm,
(space_limit/1024/1024) sl,
(space_used/1024/1024) su,
(space_reclaimable/1024/1024) sr,
number_of_files nf,
(space_used/space_limit)*100 pc
from v$recovery_file_dest;
select * from v$flash_recovery_area_usage;
V$RECOVERY_FILE_DEST
This view is the main source and contains the following columns:
SPACE_LIMIT how much space has been allocated to the flash recovery area
SPACE_USED space occupied
SPACE_RECLAIMABLE how much space you can reclaim by getting rid of obsolete and redundant files in the flash recovery area.
NUMBER_OF_FILES number of files
V$FLASH_RECOVERY_AREA_USAGE
This view provides information about the flash recovery area disk space usage. Following is its main columns:
FILE_TYPE the type of the file and can have any of the following values: controlfile, onlinelog, archivelog, backuppiece, imagecopy, flashbacklog
PERCENT_SPACE_USED This represents the disk space used by the file type, in percentage.
PERCENT_SPACE_RECLAIMABLE this represents the percentage of disk space reclaimable from the file type after deleting any obsolete or redundant files, and files backed up to a tertiary device.
Comments are currently closed.