To check space in Recovery Area
SET LINE 200
COL NAME FORMAT A50
SELECT NAME,
SPACE_LIMIT/1024/1024 Mb,
SPACE_USED/1024/1024 Mb_used,
SPACE_RECLAIMABLE/1024/1024 MB_SPACE_RECLAIMABLE,
(SPACE_LIMIT-SPACE_USED)/1024/1024 Mb_left
FROM V$RECOVERY_FILE_DEST;
To check area usage
SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 3.07 0 14 0
BACKUP PIECE .39 0 1 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
Enabling Flash Recovery Area
1 to Set up the size
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='*';
2 to Set up the Location
file system
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/disk1/flash_recovery_area' SCOPE=BOTH SID='*';
or ASM
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+data' SCOPE=BOTH SID='*';
Disabling Flash Recovery Area
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID="*";
