************************************************
* Description: SQL for Querying FRA Utilization
* Compatiablity: RDBMS 10g, 11g, 12c
* Date: 18:06 PM EST, 01/09/2017
************************************************



SQL> SELECT ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 
     ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 
     ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
     SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
     FROM V$RECOVERY_FILE_DEST A, V$FLASH_RECOVERY_AREA_USAGE B
     GROUP BY SPACE_LIMIT, SPACE_USED, SPACE_RECLAIMABLE;
	 
	 
         FLASH_IN_GB FLASH_USED_IN_GB FLASH_RECLAIMABLE_GB PERCENT_OF_SPACE_USED
         ----------- ---------------- -------------------- ---------------------
                 50              .52                  .03                  1.04
			 
			 

SQL> set linesize 200;
     select * from v$flash_recovery_area_usage;
	 
         FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
         -------------------- ------------------ ------------------------- ---------------
         CONTROL FILE                          0                         0               0
         REDO LOG                              0                         0               0
         ARCHIVED LOG                        .36                       .05               6
         BACKUP PIECE                        .91                         0               9
         IMAGE COPY                            0                         0               0
         FLASHBACK LOG                         0                         0               0
         FOREIGN ARCHIVED LOG                  0                         0               0

	
	

Your Comments