Oracle db: I want to monitor the FRA used in GB, but I have some problem for the PCT-CodePudding

On Oracle those lines show me the use of FRA, in mb

col name format a7
clear breaks
clear computes

select name
,      round(space_limit / 1024 / 1024) size_mb
,      round(space_used  / 1024 / 1024) used_mb
,      decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used
from v$recovery_file_dest
order by name
/

I want GB values, this modification works, i have only problem for pct_used

col name format a7
clear breaks
clear computes

select name
,      round(space_limit / 1024 / 1024 / 1024) size_gb
,      round(space_used  / 1024 / 1024 / 1024) used_gb
,      decode(nvl(space_used,0),0,0,round(() * 10)) pct_used
from v$recovery_file_dest
order by name
/

I have tried

,      decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 10)) pct_used

,      decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 1000)) pct_used

but both fails, how to obtain the % used for gb values?

CodePudding user response：

Solution found

col name format a7
clear breaks
clear computes

select name
,      round(space_limit / 1024 / 1024 / 1024) size_gb
,      round(space_used  / 1024 / 1024 / 1024) used_gb
,      round((space_used/space_limit) * 100) pct_used
from v$recovery_file_dest
order by name
/

Give me 44% of space used in both times, so is working fine

SQL> select name
  ,      round(space_limit / 1024 / 1024 / 1024) size_gb
  ,      round(space_used  / 1024 / 1024 / 1024) used_gb
  ,      round((space_used/space_limit) * 100) pct_used
  from v$recovery_file_dest
  order by name
  /

NAME   |   SIZE_GB|   USED_GB|  PCT_USED
-------|----------|----------|----------
/var/or|    20|     9|        44
acle/ap|      |      |
p/oracl|      |      |
e2     |      |      |

col name format a7
clear breaks
clear computes
select name
  ,      round(space_limit / 1024 / 1024) size_mb
  ,      round(space_used  / 1024 / 1024) used_mb
  ,      decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used
  from v$recovery_file_dest
  order by name
  /

NAME   |   SIZE_MB|   USED_MB|  PCT_USED
-------|----------|----------|----------
/var/or|     20480|  9096|        44
acle/ap|      |      |
p/oracl|      |      |
e2     |      |      |