67 lines
2.8 KiB
MySQL
67 lines
2.8 KiB
MySQL
|
/*
|
||
|
|
*
|
||
|
|
* Author : Vishal Gupta
|
||
|
|
* Purpose : Display Recovery Area Usage
|
||
|
|
* Compatibility : 11.2 and above
|
||
|
|
* Parameters : NONE
|
||
|
|
*
|
||
|
|
* Revision History:
|
||
|
|
* ===================
|
||
|
|
* Date Author Description
|
||
|
|
* --------- ------------ -----------------------------------------
|
||
|
|
* 22-Jan-16 Vishal Gupta Added total computation
|
||
|
|
* 02-May-13 Vishal Gupta Updated to display space used in GB
|
||
|
|
* 30-Jul-12 Vishal Gupta Created
|
||
|
|
*
|
||
|
|
*/
|
||
|
|
|
||
|
|
PROMPT *****************************************************************
|
||
|
|
PROMPT * R E C O V E R Y A R E A U S A G E
|
||
|
|
PROMPT *****************************************************************
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
||
|
|
COLUMN name HEADING "Name" FORMAT a28
|
||
|
|
COLUMN value HEADING "Value" FORMAT a15
|
||
|
|
COLUMN type HEADING "Type" FORMAT a10
|
||
|
|
COLUMN ordinal HEADING "Ordinal" FORMAT 99
|
||
|
|
COLUMN isdefault HEADING "Def|ault?" FORMAT a5
|
||
|
|
COLUMN ismodified HEADING "Modi|fied?" FORMAT a8
|
||
|
|
COLUMN isadjusted HEADING "Adjus|ted?" FORMAT a5
|
||
|
|
|
||
|
|
SELECT p.inst_id
|
||
|
|
, p.name
|
||
|
|
, TRIM(TO_CHAR(ROUND(p.value / 1024 / 1024 / 1024),'999,999') || ' GB') value
|
||
|
|
FROM gv$system_parameter p
|
||
|
|
WHERE p.name = 'db_recovery_file_dest_size'
|
||
|
|
ORDER BY p.inst_id
|
||
|
|
;
|
||
|
|
|
||
|
|
COLUMN file_type HEADING "File Type" FORMAT a25
|
||
|
|
COLUMN number_of_files HEADING "Files (#)" FORMAT 999,999,999
|
||
|
|
COLUMN space_used HEADING "Space|Used|(MB)" FORMAT 999,999,999
|
||
|
|
COLUMN space_reclaimable HEADING "Space|Reclaimable|(MB)" FORMAT 999,999,999
|
||
|
|
COLUMN percent_space_used HEADING "Space|Used|(%)" FORMAT 999.99
|
||
|
|
COLUMN percent_space_reclaimable HEADING "Space|Reclaimable|(%)" FORMAT 999.99
|
||
|
|
|
||
|
|
BREAK ON REPORT
|
||
|
|
|
||
|
|
COMPUTE SUM LABEL 'Total' OF number_of_files FORMAT 999,999,999 ON REPORT
|
||
|
|
COMPUTE SUM LABEL 'Total' OF space_used FORMAT 999,999,999 ON REPORT
|
||
|
|
COMPUTE SUM LABEL 'Total' OF space_reclaimable FORMAT 999,999,999 ON REPORT
|
||
|
|
COMPUTE SUM LABEL 'Total' OF percent_space_used FORMAT 999.99 ON REPORT
|
||
|
|
COMPUTE SUM LABEL 'Total' OF percent_space_reclaimable FORMAT 999.99 ON REPORT
|
||
|
|
|
||
|
|
SELECT r.file_type
|
||
|
|
, r.number_of_files
|
||
|
|
, ROUND(r.percent_space_used * p.value / 100 / 1024 / 1024) space_used
|
||
|
|
, ROUND(r.percent_space_reclaimable * p.value / 100 / 1024 / 1024) space_reclaimable
|
||
|
|
, r.percent_space_used
|
||
|
|
, r.percent_space_reclaimable
|
||
|
|
FROM v$recovery_area_usage r
|
||
|
|
, v$system_parameter p
|
||
|
|
WHERE p.name = 'db_recovery_file_dest_size'
|
||
|
|
;
|
||
|
|
|